Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, because DAX is the most used language usedin numerous computationsin Power BI, many do not have any idea about this feature of Power Query. In this blog, I will explain how simple the process is calculateAge in Power BI with Power BI. This methodis very helpful for situations in which you require be able to calculate the computations of agecan be carried out by calculating the row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table that's included in the AdventureWorksDW table and is the birthdate column. I've removed a few columns that weren't needed to make it easier to read;

To calculate the age of each client is, all you'll need to do is:

• In Power BI Desktop, Click on Transform Data
• In the Window of the Power Query Editor; select the first column. birthdate.
• To add a column, click on the Column Tab next to the "From Date & Time" section, then under Date Choose the appropriate age range.

That's about it. This is how you calculate what's the ratio of the Birthdate column as well in the current date and time.

However, the date that appears in the Age column does not actually appear to be an actual age. This is because it's an actual length.

Duration

Duration is a specific type of data utilized within Power Query which represents the difference between two DateTime values. Duration is composed from four different numbers.

days.hours.minutes.seconds

This is the way to look up the values above. From the point of view of the user it's not their responsibility to understand the specifics of this. There are methods to be used to calculate each fraction of the time. Selecting the Duration menu you'll see the amount of minutes, seconds, hours, months and days from it.

to assist for calculating the age in years like, for instance, 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 an annual number.

Rounding

The final thing to note is that nobody says that they are 53.813698630136983! They say 53, which is rounded down. It's very easy to select Rounding, and then the round down option in the Transform tab.

This will give you the age of the years:

Then, you can clean the other columns in the event that you'd like (or this could mean because you applied transformations to the Transform tab to avoid the creation of new columns) The column could be named Age Column Age.

Things to Know

• Refresh The age calculated using this method will be updated as you refresh your data. Each time, it will be matched the date of birth to the date and the time when the data refresh occurred. This method can be described as an algorithm used for pre-calculating the age. If you require the calculation of age to be performed dynamically by DAX Here's a description of the method you could use.
• What's the explanation? Power Query benefits of the calculation of age using The benefit of using age calculation with Query is that the calculation takes place during the refresh of your report. It's accomplished using an application that facilitates the calculation to do, and there's no extra overhead to do it using DAX as a measurement of runtime.
• Other scenarios There isn't the only method to calculate age just by birthdate. It can be used to calculate the age of inventory in products and also to determine the differences between dates and times that are different from one.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc of Computer engineering. He is more than twenty years old and has worked in the area of data analysis, BI, databases, programmingand development, mainly using Microsoft technologies. Reza has been an official Microsoft Data Platform MVP for nine years in a row (from 2011 to now) due to his dedication toward Microsoft BI. Reza is a prolific author and co-founder of RADACAD. Reza is also co-founder and co-organizer of 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 a couple of titles related to MS SQL BI and also is writing other books. He also was a frequent member of technical forums on the internet such as MSDN as well as Experts-Exchange and was moderator for the MSDN SQL Server forums, and holds the MCP and MCSE , as well as the MCITP for Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. In addition, he's also the author of the highly loved publication Power BI from Rookie to Rock Star, which is free and contains nearly 17000 pages of content 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 as well as SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help you discover the most effective data solution. He is an avid Data enthusiast.This post was filed under 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. The following entry was filed under Power BI.

Post navigation

Use different visual pages to share using various security groups within the Power BIAge's Year Calculation that is used to calculate the Leap Year in Power BI through Power Query