Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, as DAX is the most popular language usedin many calculationsin Power BI, many do not realize this feature offered by Power Query. In this blog post, I'll show you how easy it is to calculateAge in Power BI by using PowerBI. This methodis extremely efficient when the estimation of the agecan be calculated on a pre-calculated row or on a row basis.

Calculate Age from a date

Below is the DimCustomer table, which is part of the AdventureWorksDW table. It also has an entry for birthdates. I've removed several of the columns that don't need to be so that it is easier to understand;

To calculate the average age of every buyer, the following information is required:

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

That's it. this calculates the calculate the amount which is the total of the column for birthdate, Birthdate column, as well as the date and time.

However, the date that is displayed in the Age column, does not really appear to be an age. This is due to the fact that it's an actual Duration.

Duration

Duration is a special kind of data format used in Power Query which represents the distinctions between the two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

And that's how you see the numbers above. But from the viewpoint of the user, it's not expected of them to comprehend the details like that. There are methods that are able to get each segment of the duration. By using the Duration menu, you'll notice that you can remove the amount of seconds or minutes, hours, days , and years from it.

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

The duration is measured in days . Then, it was divided in 365days to give you the annual value.

Rounding

At the final point, no one proclaims their age in 53.813698630136983! The number is 53. This is the number rounded down. Select Rounding or Round Down in the Transform tab for it.

This will show you what your age is in terms of years

You can clean other columns, if you want (or the reason could be that you applied transformations in the Transform tab to prevent the creation of new columns) You can also name this column Age:

Things to Know

• Refresh The age of the data calculated this way will be refreshed each time you refresh your database. Every time, the system will be able to compare the birthdate to the date and timing of the refreshing. The method uses an algorithm for pre-calculating the age. If you would like the calculation to be performed dynamically using DAX here's how I explained how to make use of.
• The motive behind Power Query: Benefits of using the age calculation feature in Power Query is that the calculation takes place when you refresh your report. This is made possible by an application that makes the calculation easy and quicker, and there's not any additional expense to calculate it using DAX for a measurement of runtime.
• Other scenarios are not intended for the calculation of age from the birthdate. It is possible to calculate the date of inventory age for items and also the differing dates and dates of each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds an BSc with a major with a concentration in Computer engineering. There are more than 20 years experience working in the field of data analysis database, BI, creating, and programming mostly with Microsoft technologies. He was an Microsoft Data Platform MVP for nine consecutive years (from 2011 until today) for his passion for Microsoft BI. Reza is a prolific author and co-founder at RADACAD. Reza is also co-founder and co-organizer of the Difinity event at 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 working on other books. He was also a regular participant on technical forums on the internet such as MSDN and Experts-Exchange and was a moderator on the MSDN SQL Server forum, and is an MCP and MCSE as well as an MCITP in BI. He also is the leader for the New Zealand Business Intelligence users group. They are also the authors of the book that is loved by many. Power BI from Rookie to Rock Star, which is completely free and includes greater than 1700 page of information and an additional book called Power BI Pro Architecture published by Apress.
They are an International Speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, and SQL Group for Users. And He is a Microsoft Certified Trainer.
Reza's ambition is to help users find the best solutions for data, and Reza is an avid Data enthusiast.This blog entry was filed under Power BI, Power BI from Rookie to Rockstar, Power Query and is classified under Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic resource to save to your bookmarks.

Post navigation

Share Different Visual Pages with different Security Groups. PowerBIAge in Years Calculation that works for Leap Year in Power BI with Power Query