Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, as DAX is the preferred language usedin numerous analysesin Power BI, many do not have knowledge of this feature inside Power Query. In this blog post I'm going to show how easy it is to calculateAge in Power BI in conjunction with PowerBI. The methodis very useful for situations in which you need be able to calculate the estimated age.can be done on an earlier calculated row by row basis.

Calculate Age from a date

Here's the DimCustomer table, which belongs to the AdventureWorksDW table that is the column with the birth date. I've removed the other columns , to make it more readable;

If you want to calculate an age range for each customer, the only thing you have to do is:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window, start by selecting the Birthdate column.
• Click on the Add Column Tab, under "From Date & Time" section, and under Date, choose the age range.

It's that simple. it. This is the method you calculate any changes in the Birthdate column as well as the current date and time.

However, the date that is displayed in"Age", but the Age column, and doesn't seem to be an actual number. It's because it's an actual Duration.

Duration

Duration is a specific type of data of the data type used in Power Query which represents the difference between the two DateTime values. Duration is composed made up of 4 numbers

days.hours.minutes.seconds

And that's how you'll get the information above. From a user's perspective, don't expect them to read details like those above. There are a variety of ways to get each portion of the time. If you select the Duration menu, you will discover that you can take the duration of seconds and minutes in addition to hours, days and years from it.

For assistance in calculating the age in years for instance , just click Total Year:

It is important to note that the duration is calculated in terms of days. After that, it is then divide by day number to give you the yearly value.

Rounding

In the end, nobody claims that their age is 53.813698630136983! They say 53, then they round it down. It's simple to select Rounding and then round down on your Transform tab.

This will give you the age of the years:

Then , you can clean up the other columns you'd like to (or it could be that you've utilized transforms within the Transform tab to avoid creating new columns.) You could name this column Age.

Things to Know

• Refresh The age that is calculated by this method will be updated as of the date when you refresh your dataset. And every refresh, it will check the birth date with the date and time for the refreshing. This method is an earlier calculation of age. If, however, you want the calculation to be performed dynamically using DAX This is what I have described how to utilize.
• What's the cause? Power Query The benefit of the age calculation feature within Power Query is that the calculation is carried out each time you refresh your report. This is accomplished using an algorithm which makes the calculation easier and faster, and there's no additional cost to calculate it with DAX for a measurement of runtime.
• Alternative scenarios It's not an accurate method to determine age based on the date of birth. This can be used to calculate the time of inventory for items and also for the distinction from two date or times one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He earned an BSc in Computer engineering. He is more than twenty years of experience in the field of data analysis, database programming, BI, and development mostly with Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years in a row (from 2011 to today) because of his devotion toward Microsoft BI. Reza is a prolific writer and co-founder for RADACAD. Reza is also co-founder as well as co-organizer for the Difinity event and Difinity Conference of New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He has written several books on MS SQL BI and also is currently writing several additional. He also was a frequent forum participant on online forums for technical issues such as MSDN and Experts-Exchange and was also moderator of MSDN SQL Server forums, and is a holder of an MCP, MCSE, and the MCITP for BI. He is also the leader of the New Zealand Business Intelligence users group. Also, he's the author of the widely acclaimed work Power BI from Rookie to Rock Star, which is free and has hundreds of pages of content and The Power BI Pro Architecture published by Apress.
It is an International Speaker for 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 dream is to help users find the best information solution. He is Data enthusiast.This post was categorized with Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic reference to bookmark.

Post navigation

Share different visual pages by using Different Security Groups in Power BI's BIAge's years calculation that is used for Leap Year in Power BI through Power Query