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 computationsin Power BI, many don't know about this feature of Power Query. In this blog , I'll describe how simple it is to calculateAge in Power BI by using PowerBI. It's a methodis highly efficient in cases where the estimation of agecan be calculated on a pre-calculated row and the row basis.

Calculate Age from a date

Below, you will see the DimCustomer table which forms part of the AdventureWorksDW table. It also has an entry for birthdates. I've removed some of the columns that aren't necessary in order to make it easier for you to read;

To calculate the average age of every buyer, you need is:

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

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

But, the age appears on the Age column, however, doesn't seem to be an actual age. It's because it's an actual duration.

Duration

Duration is an exclusive type of data structure within Power Query which represents the variations in two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

That's the way you see the numbers above. But from the standpoint of the user, it's not the norm for them to know the specifics of that. There are methods to ensure that you can access each portion of the time. using the Duration menu option you'll see that you are able to determine the number of seconds and minutes, hours, the days and years out of it.

To aid in calculating the age in years by way of example it is easy to click on Total Years:

The duration is calculated in days and was then divided in 365days to provide you with the annual value.

Rounding

The bottom line is that nobody declares your age as 53.813698630136983! They are saying 53, which is an inverse of 53. Select Rounding or Round Down by clicking on the Transform tab.

This will show you the age you've reached in your years.

You can clean other columns, if you want (or there could be because you applied transformations in the Transform tab, to prevent creating new columns) You can name this column Age:

Things to Know

• Refresh The data's age determined by this method will be updated during the time of refreshing your database. Each time, the system will be able to compare the birthdate with the date as well as the time during the process of refreshing. That method will be an algorithm to predict the age. If you'd like to have the age calculation to be done in a dynamic manner using DAX this is the method I explained the best way to make use of.
• The main reason for Power Query: Benefits from using age calculation in Power Query is that the calculation is done during the process of refreshing your report. This is accomplished using an application that makes calculations more efficient and speedier, and there's no additional cost in calculating it using DAX for a measurement of runtime.
• Other scenarios This is not designed to calculate the date of birth. It is possible to calculate the time of inventory on products as well as to calculate the different 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 has a BSc with a concentration on Computer engineering. The engineer has over 20 years' expertise in the area of data analysis database, BI, programming, and developing mostly using Microsoft technologies. He has been an Microsoft Data Platform MVP for nine continuous years (from 2011 to the present) for his passion for Microsoft BI. Reza is an incredibly prolific author and co-founder at RADACAD. Reza is also the co-founder of 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 books on MS SQL BI and also is writing additional books. He was also a regular member of technical forums online such as MSDN and Experts-Exchange and was a moderator for MSDN SQL Server forum as well as an MCP and MCSE as well as an MCITP in BI. He also is the leader of the New Zealand Business Intelligence users group. The group is also creator of the book that is highly praised Power BI from Rookie to Rock Star, which is absolutely free and contains an additional 1700+ pages of material and a second book titled 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 goal is to help users discover the most effective options for data. he's an avid Data enthusiast.This 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 great guide to bookmark.

Post navigation

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