Power Query has a simple way of calculating the age. However, because DAX is the popular language in many calculations in Power BI, many are not aware of this function in Power Query. In this blog article, I am going to show you how easy is to calculate Age in Power BI using Power BI. This method is very useful when the age calculation can be done as a pre-calculated row by row basis.
Calculate Age from a date
Below is the DimCustomer table from AdventureWorksDW table which as a birthdate column. I have removes some of the extra columns to make it easier to read;
To calculate the age of each customer, all you need is to:
- In Power BI Desktop, Click on Transform Data
- In Power Query Editor window; select the Birthdate column first
- go to Add Column Tab, under “From Date & Time” section, and under Date, select Age
That’s it. this will calculate the difference between the Birthdate column, and the current date and time.
However, the age that you see in the Age column, doesn’t really look like an age. That is because it is a Duration.
Duration is a specific data type in Power Query which represents the difference between two DateTime values. duration is a combination of four values:
and that is how you can read the values above. However, for the user’s point of view, you don’t expect them to go and read the details like that. there are ways that you can fetch each part of the duration. using the Duration menu option, you will see that you can extract the number of seconds, minutes, hours, days, and years from it.
For calculating the age in years, for example, you can simply click on Total Years:
Note that the duration is calculated in days and then divided by 365 to give you the yearly value.
In the end, no one says their age as 53.813698630136983! they say it as 53, rounded down. You can easily select the Rounding and round down from the Transform tab for it.
This will give you the age in years:
You can then clean the other columns if you want (or you could have used transformations from the Transform tab to avoid the creation of new columns), and call this column; Age:
Things to Know
- Refresh: The age calculated this way will get updated at the time of refreshing your dataset. and each time will compare the birthdate with the date and time of the refresh. This method is a pre-calculation of an age. If you, however, need the age calculation to be done dynamically using DAX, here I explained a way that you can use.
- Why Power Query: Benefits of doing age calculation in Power Query is that the calculation is done at the time of refreshing your report, using a tool that makes the calculation simpler, and there won’t be extra overhead of calculating it using DAX as a measure runtime.
- Other scenarios: this is not for calculating age only from the birthdate. this can be used for product inventory age and the difference between two dates and times from each other.