Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that's a straightforward method to calculating the age. But, since DAX is the largest and most well-known language usedin several calculationsin Power BI, a lot of people are unaware of the functions available in Power Query. In this blog post, I'll explain how easy you can calculateAge within Power BI with PowerBI. This methodis extremely useful in situations where it is required to calculate the age calculation. Itcan be done using an earlier calculated row-by-row basis.

Calculate Age from a date

It's an example of the DimCustomer table , and it comes taken from the AdventureWorksDW table, which acts like the birthday date column. I've eliminated columns that aren't required to make it easier to read;

To calculate the age of each customer, you will need:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window, make sure that you select the first column, namely the Birthdate.
• Click on the Add Column Tab that is in the "From Date & Time" section. Under Date, choose Age

This is all there is to it. This will calculate any differences between that column, the Birthdate column, as well as the current date and time.

But, the age you see when looking in the Age column doesn't appear like an actual age. It's because it's a real duration.

Duration

Duration is a particular kind of data utilized to calculate the duration of a query in Power Query which represents the differences between the two DateTime values. Duration is a mixture of four different values:

days.hours.minutes.seconds

This is what you'll find in the above information. But from a user's viewpoint, they shouldn't be required to read information like those above. There are ways to extract each part of the time. By using the Duration menu, you'll see that you can get the number of minutes, seconds, hours, days , and years from it.

To aid in calculating the age in years for instance , it is simple to calculate the Total Year:

It is important to note that the duration is measured in days , and divided by 365 days to give the annual amount.

Rounding

There is no truth in it, and nobody states that their kid's age is 53.813698630136983! They state 53, which is rounding down. It's easy to choose the Rounding and then round down in the Transform tab for it.

This will give you the years of experience:

It's then possible to remove other columns if you'd like (or there's a possibility that you've applied transformations in the Transform tab to prevent the creation of new columns) This column is then referred to as column Age.

Things to Know

• Refresh The age that is calculated in this manner will be altered at the time of refreshing your data. Every refresh, the data will be matched to the birthdate with the date and time that the refresh of data took place. This method is a way to calculate earlier of age. However, if you would like the calculation of age to be performed dynamically by using DAX This is the method I've described how to use DAX.
• The motivation for using Power Query: Benefits of calculating an age by using Power Query is that the calculation takes place whenever you refresh your report. The calculation is performed using an algorithm which makes the calculation easy, and there will not be any additional overhead when calculating it with DAX to measure of runtime.
• Different scenarios. This isn't meant to be used for calculation of the age of a person based on their birth date. This can be used to calculate inventory of products and different dates and dates from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has an BSc in Computer engineering. He has more than 20 years of expertise in data analysis, programming, databases, BI and development, mostly employing Microsoft technologies. He was an Microsoft Data Platform MVP for nine years in a row (from 2011 until the present) because of his commitment to Microsoft BI. Reza is a regular blog author, and is the director and co-founder of RADACAD. Reza is also the co-founder and organizer of Difinity the conference that is held within 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 writing other books. He was also a frequent participant in forums online for technical issues , such as MSDN and Experts-Exchange as well as moderator for the MSDN SQL Server forums, and holds an MCP and MCS as well as an MCITP in Business Intelligence. He was the creator of the New Zealand Business Intelligence users group. Additionally, he's the author of the highly acclaimed publication Power BI from Rookie to Rock Star, which is accessible for free and contains more than 17000 pages of information and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
This speaker has been an International speaker for 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 goal is to help you find the best solutions for data. He's an avid Data enthusiast.This post was published in 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 source to bookmark.

Post navigation

Share different visual pages with different security groups in Power the BIAge's Calculation of Years that is used to calculate Leap Year in Power BI by making use of Power Query