Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, as DAX can be the most commonly used language usedin numerous computationsin Power BI, many do not realize this feature available in Power Query. In this blog, I will show how easy you can calculateAge within Power BI with Power BI. This methodis very useful when you need to calculate the computations of agecan be performed on an already calculated row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table that is one of AdventureWorksDW table and is the birthdate column. I've eliminated some columns that weren't needed to make it more readable;

In order to calculate the age of each client, the only thing you have to do is to:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; choose the first column, Birthdate.
• To add a column, click on the Column Tab Then, under the "From Date & Time" section, and then under Date select the age range.

This is all there is to it. This is the method to calculate what is the ratio between Birthdate column, as well in the current date and time.

However, the age shown in the Age column, but it doesn't appear to be an actual age. It's because it's an actual time period.

Duration

Duration is one of the types of data used to calculate the duration of Power Query which represents the differences in Two DateTime values. Duration is made up from four different numbers.

days.hours.minutes.seconds

This is how you understand the above numbers. But from the perspective of the user it's not required of them to comprehend the details of this. There are methods that can find each segment of time. Selecting the Duration menu you'll be able see the quantity of minutes, seconds, hours, days and years from it.

In order to aid with calculating the age in years for example, it is simple to choose Total Years.

It is vital to keep in mind that time is expressed in terms of days and then divided by the number of days, to give you the annual amount.

Rounding

Final note: nobody claims that your age is 53.813698630136983! They state 53, which is the number rounded down. It's simple to select Rounding and then choose the round down option in the Transform tab.

This will show you the year of birth:

Then, you can tidy the other columns, If you'd like (or you could find that you applied transformations to the Transform tab to prevent creating new columns) The column could be renamed to Age column Age.

Things to Know

• Refresh The age that is calculated in this way will be updated as you refresh your data. Each time, it'll match the date of birth with the date and the time when the data refresh occurred. This method will be an algorithm used for pre-calculating the age. If, however, you want the calculation of the age to be performed dynamically by DAX Here's a description of the method that you could use.
• What's the explanation? Power Query The benefit of using age calculation in the Power Query is that it is calculated when you refresh your report. It is done using an application which makes the calculation simpler and there is no additional cost in doing it using DAX as a measure of running time.
• Other scenarios There isn't a method to calculate age just by birthdate. This method can be used to calculate the age of inventory in products and the variations between two dates or times from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds an BSc of Computer engineering. He has over twenty years old and has worked in the area of data analysis, programming, BI, databases and development, mainly using Microsoft technologies. He was an official Microsoft Data Platform MVP for nine years in a row (from 2011 to the present) for his dedication for Microsoft BI. Reza can be described as an prolific writer and is co-founder of RADACAD. Reza is also co-founder and co-organizer of the Difinity Conference located in 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 works on MS SQL BI and also is writing other books. He also was a frequent forum participant on technical forums like MSDN and Experts-Exchange and was moderator for the MSDN SQL Server forum and holds the MCP and the MCSE as along with the MCITP for BI. He is the leader of the New Zealand Business Intelligence users group. In addition, he's responsible for the creation of the highly loved volume Power BI from Rookie to Rock Star, which is free and contains nearly 17000 pages and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
The speaker is an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, and SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help you to find the most effective data solution. He is a Data enthusiast.This article was published by Reza on Power BI, Power BI from Rookie to Rockstar, Power Query and was tagged as Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. You can follow any responses to this entry through the RSS feed.

Post navigation

Share visual pages on different security groups in PowerBIAge's Year Calculation that is used to calculate the Leap Year in Power BI by using Power Query