Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, since DAX is the most popular language usedin several functionsin Power BI, many are unaware of this function in Power Query. In this article I'm going to teach you how to calculateAge in Power BI with Power BI. This methodis very useful in situations where you need to calculate the computation of an agecan be done as an earlier calculated row by row basis.

Calculate Age from a date

The table below is DimCustomer table in the AdventureWorksDW table which as a birthdate column. I have removes some of the extra columns to make it more readable;

To calculate your age for each buyer, you have to do is:

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

That's it. This is how you calculate any difference in the Birthdate column and the date and time.

However, the age that appears when you look at the Age column, doesn't really appear to be an age. It's because it's a Duration.

Duration

Duration is a unique kind of data type used within Power Query which represents the difference between two DateTime values. Duration is a combination of four numbers:

days.hours.minutes.seconds

and that's what you will see in the information above. From the perspective of the user you don't want them to search for details like the ones above. There are ways you are able to get each segment from the length. By selecting the Duration menu you'll notice the number of seconds and minutes, hours, days and years out of it.

To use the method of calculating the age in years for instance, you can simply go to Total Years.

The duration is calculated in terms of days. It is then divided by 365 to provide you with the annual value.

Rounding

The bottom line is that nobody declares your age in 53.813698630136983! They refer to it as 53, then rounded down. It is simple to choose Rounding and then round down option from the Transform tab for it.

This will provide you with the number in years:

Then you can tidy the other columns if you want (or perhaps you've made use of transformations in the Transform tab to avoid creating new columns) Then, you can call this column; Age:

Things to Know

• Refresh The date calculated this way will be changed at the time of refreshing your dataset. Each time, it will match the birthdate to the date and time at the time of refresh. This method is a pre-calculation of the age. If you require the calculation of age to be dynamically done using DAX This is the way I've explained an approach you can utilize.
• What is the reason? Power Query: Benefits of performing age calculations in Power Query is that the calculation takes place when you refresh your report. The report is refreshed using an instrument that makes the calculation much easier and faster, and there won't be extra overhead of calculating it using DAX as a measure runtime.
• Another scenario This is not a method to calculate age just from the birthdate. It can be used to calculate stock-level age for inventory items and the differences between two dates and dates from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc of Computer engineering; he is more than twenty years' experience in the field of data analysis databases, BI, programming, and development mostly focused on Microsoft technologies. He is an official Microsoft Data Platform MVP for nine consecutive years (from 2011 to the present) for his dedication in Microsoft BI. Reza is a frequent writer and is co-founder with RADACAD. Reza is also the co-founder and co-organizer of Difinity event which is held 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 a few books on MS SQL BI and also is working on other books. He was also an active participant on technical forums online like MSDN and Experts-Exchange as well as moderator of MSDN SQL Server forums, and holds the MCP and MCSE as well as an MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the writer of the well-known book Power BI from Rookie to Rock Star, which is free with more than 1800 pages of material and the Power BI Pro Architecture published by Apress.
His credentials include being an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, and SQL Users Groups. And He is a Microsoft Certified Trainer.
Reza's dream is to help you find the best data solution, he is Data enthusiast.This article was published on Power BI, Power BI from Rookie to Rockstar, Power Query and tagged Power BI, Power BI from Rookie to Rock Star, Power Query. You can follow any comments to this entry through the RSS feed.

Post navigation

- Share Different Visual Pages with Different Security Groups in Power BIAge's Year Calculation that works for Leap Year in Power BI using Power Query -