Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, since DAX is the most popular language usedin many calculationsin Power BI, many do not understand this capability provided by Power Query. In this article, I'll show you how easy to calculateAge in Power BI using PowerBI. It's a methodis extremely efficient in cases where the estimation of the agecan be based on a pre-calculated row and basis.

Calculate Age from a date

Below, you will see the DimCustomer table which makes up the AdventureWorksDW table. It has a birthdate column. I've removed some of the columns which aren't needed to make it easier to read;

In order to calculate the actual time of each customer's purchase, you'll need:

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

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

However, the number of years as a number in the Age column, it doesn't actually appear to be an age. It's because it's an actual duration.

Duration

Duration is a special kind of data structure within Power Query which represents the distinctions of the two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

and that's how you read the numbers above. But from the viewpoint of the user it is not required of them to know the specifics of that. There are methods that can get every portion of the duration. With the Duration menu there is a way to remove the amount of seconds and minutes, hours, days , and years from it.

For assistance in calculating the age in years using an example, it is easy to click on Total Years:

The duration was calculated as days and divided by 365 to give you the annual value.

Rounding

It's the truth, nobody proclaims their age in 53.813698630136983! The number is 53. This is the number rounded down. You can select Rounding as well as Round Down in the Transform tab for it.

This will tell you how old you are:

You can then cleanse the other columns, if desired (or you could find because you applied transformations in the Transform tab, to prevent creating new columns.) The column can be named column Age:

Things to Know

• Refresh The data's age that is calculated by this method will be updated each time you refresh your database. Every time it is refreshed, the system will be competent to match the birthdate to the date and the time in the process of refreshing. The method uses an algorithm to predict the age. If you'd like the age calculation to be performed dynamically using DAX here's the way I explained what you can make use of.
• The reason behind Power Query: Benefits from using age calculation in Power Query is that the calculation is performed during the process of refreshing your report. This is done by using an instrument that makes calculation easy and quicker, and there's no extra cost in the calculation using DAX as a measure runtime.
• Other scenarios These are not designed to calculate the date of birth. It is possible to calculate the age of inventory for products as well as to calculate the different dates and dates from one other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc with a major with a concentration in Computer engineering. The engineer has over 20 years' experience working in the field of data analysis and BI, databases, creating, and programming mostly with Microsoft technologies. He has been a Microsoft Data Platform MVP for nine continuous years (from 2011, to now) because of his love of Microsoft BI. Reza has been a prolific writer and co-founder of RADACAD. Reza is also the co-founder and 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 books on MS SQL BI and also is writing other books. He was also an active participant in online forums for technical issues like MSDN and Experts-Exchange and was moderator of MSDN SQL Server Forums He is also an MCP, MCSE and as an MCITP for BI. He is also the head for the New Zealand Business Intelligence users group. The group is also creator of the book very well-loved Power BI from Rookie to Rock Star, which is entirely free and comprises an additional 1700+ pages and an additional book called Power BI Pro Architecture published by Apress.
There 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 desire is to help users find the most effective solutions for data, and the reason he's a Data enthusiast.This post 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 for you to bookmark.

Post navigation

Share Different Visual Pages through different Security Groups. Power BIAge is a Years Calculation , which works for Leap Year in Power BI with Power Query