Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. But, since DAX is the main language usedin numerous calculationsin Power BI, a lot of people do not know about the function available in Power Query. In this blog , I'll go over how simple to calculateAge in Power BI with Power BI. This methodis extremely useful when you need to calculate your analysis of the agecan be performed on a pre-calculated row-by- rows.

Calculate Age from a date

This is the DimCustomer table that is part of the AdventureWorksDW table, which acts in the role of the date column. I've removed the columns that aren't needed for the sake of making it easier to be read.

To calculate your age for each customer, all you need is:

• In Power BI Desktop, Click on Transform Data
• On the Power Query Editor window; start by selecting the column titled Birthdate.
• click on the Add Column Tab, choose"Add Column Tab," then click on the "From Date & Time" section. Under Date, choose the appropriate age.

This is it. this does calculate any change from the Birthdate column and also the current date and time.

However, the age that displays in Age column, doesn't seem to look like an age. It is due to the fact that it is a Duration.

Duration

Duration is a specific kind of data type that is used in the data type of Power Query which is used to represent the differences between the Two DateTime values. Duration is made up of 4 numbers.

days.hours.minutes.seconds

This is how you find the above values. For the users' viewpoint, they shouldn't be required to find information such as those. There are methods that can obtain every single segment that's the length of time. If you click on the Duration menu you'll find that you can get the quantity of seconds to minutes, hours days, and years.

To use it for calculating the age in years, for example it is simple to calculate Total Year:

The duration is measured in days , and after that is divided by the amount of days, you will get the value for the year.

Rounding

The truth is that no one claims your age is 53.813698630136983! They call it 53, and then they round it down. You can select Rounding option before selecting the round down from the Transform tab for it.

This will provide you with the age in years:

Clean up the other columns, if desired (or there could be because you made use of transformations by using the Transform tab to prevent creating new columns) Then, name this column Age.

Things to Know

• Refresh The date that is calculated this method will be refreshed each time you're refreshing your data. Each time, the system will evaluate dates of birth and the timing and date of the refreshing. This method is a first calculation of age. If you want the age calculation to be performed dynamically using DAX This is the approach I've outlined the method to utilize.
• What is Power Query: Benefits of using age calculations in Power Query is that the calculation is performed at the moment when you refresh your report. Reports are refreshed by using an application that makes calculations more simple, and there is no additional cost to formulating it using DAX for measure of time.
• Another situation is that it isn't meant to calculate age solely by birthdate. This could be used to calculate inventory of items in addition to the variation between two dates or times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He is a BSc in Computer engineering. He has an impressive 20+ years' knowledge of data analysis and programming databases and development that is primarily on Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years in a row (from 2011 to present) because of his dedication toward Microsoft BI. Reza is a frequent blog author, and also the editor and co-founder of RADACAD. Reza is also the co-founder and co-organizer of the Difinity event, which is held within New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote several books on MS SQL BI and also is working on additional books. He was also a regular member of online forums on technical issues , such as MSDN and Experts-Exchange and was moderator of MSDN SQL Server forums, as well as an MCP and MCS as well as an MSCITP of Business Intelligence. He is the head of the New Zealand Business Intelligence users group. He is also the creator of the book , which is very well-loved Power BI from Rookie to Rock Star that is available for free and has over 1700 pages of material and an additional book called Power BI Pro Architecture published by Apress.
His qualifications are that he is an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday in addition to SQL Users Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help users find the perfect data solution. He is a data enthusiast.This post was filed as Power BI, Power BI from Rookie to Rockstar, Power Query and is covered in Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. The following entry was filed under Power BI.

Post navigation

- Share different visual pages using different Security Groups PowerBIAge's Years Calculation is able to calculate Leap Year in Power BI using Power Query