Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, since DAX is the most popular language usedin many calculationsin Power BI, many don't know about this feature that is available in Power Query. In this blog post, I'll explain how simple it is to calculateAge in Power BI with PowerBI. This methodis highly efficient when your estimation of your agecan be calculated on a pre-calculated row or the row basis.

Calculate Age from a date

Below, you will see the DimCustomer table, which forms part of the AdventureWorksDW table, which includes the birthdate column. I've removed some of the columns that aren't required in order to make it easier for you to understand;

To calculate the actual the age for each purchaser, the following information is required:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; begin by selecting the column titled Birthdate.
• Go to the Add Column Tab, and then select"Add Column Tab," then click on the "From Date & Time" section. Under Date select the age range.

That's all there is. This will calculate the amount which is the total of the column for birthdate, Birthdate column, as well as the time and date column.

However, the age that appears as a number in the Age column, but it doesn't seem to be an actual age. It's because it's an actual duration.

Duration

Duration is a distinct type of data format in Power Query which represents the distinctions in the two DateTime values. Duration is a combination of four different values:

days.hours.minutes.seconds

This is how you take the above numbers. From the perspective of the user, it's not their responsibility to comprehend the details like that. There are methods that can get every part of the duration. By using the Duration menu, you'll see that you are able to take the number of seconds minutes, hours, years and days from it.

To assist in calculating the age in years using an example, it is simple to select Total Years:

The duration is measured in days , and then divided by 365 to provide you with the annual value.

Rounding

At the final point, nobody declares your age as 53.813698630136983! The number is 53. This is round down. You can choose Rounding and Round Down on the Transform tab for it.

This will tell you how old you are:

Then, you can clean the other columns, if you wish (or you could find because you used transformations on the Transform tab to avoid creating new columns.) It is possible to name this column as Age:

Things to Know

• Refresh The age of the data calculated in this manner will be refreshed when you refresh your database. Each time it is refreshed, the system will be capable of comparing the birth date to the date and duration of refreshing. The method can be described as an algorithm that is used to pre-calculate the age. If you wish the calculation of age to be performed dynamically with DAX this is the method I explained how to make use of.
• The motivation behind Power Query: Benefits of using the age calculation feature in Power Query is that the calculation is done during the process of refreshing your report. It is accomplished by using an application that makes calculations more efficient and speedier, and there's no additional cost for calculating it using DAX to measure of runtime.
• Other scenarios are not meant to be used for calculation of age from birth date. It is possible to calculate the age of inventory in the case of products as well as the different dates and dates that differ from one other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has an BSc with a concentration of Computer engineering. More than twenty years' experience in the fields of data analysis and BI, databases, creating, and programming mostly using Microsoft technologies. He was an Microsoft Data Platform MVP for nine years in a row (from 2011 until the present) because of his love for Microsoft BI. Reza has been a prolific writer and co-founder of RADACAD. Reza is also the co-founder and co-organizer for the Difinity event 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 writing various other books. He was also a regular participant in online forums for technical issues like MSDN as well as Experts-Exchange and was a moderator for the MSDN SQL Server forum and holds the MCP and MCSE aswell being an MCITP in BI. He also serves as the leader of the New Zealand Business Intelligence users group. The group is also the creator of the book that is highly praised Power BI from Rookie to Rock Star, which is absolutely free and contains over 1700 pages of information as well as a companion book called Power BI Pro Architecture published by Apress.
The company is an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, and SQL user group. And He is a Microsoft Certified Trainer.
Reza's goal is to help users find the most effective solutions for their data. And is an avid Data enthusiast.This blog post was posted under Power BI, Power BI from Rookie to Rockstar, Power Query and is categorized under Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic source to bookmark.

Post navigation

Share different Visual Pages using different Security Groups Power BIAge Years Calculation works for Leap Year in Power BI using Power Query