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 preferred language usedin many computationsin Power BI, many do not realize this feature that is available in Power Query. In this article, I'll describe how simple to calculateAge in Power BI with PowerBI. It is a methodis highly efficient in situations where your estimation of agecan be calculated using a pre-calculated row the row basis.

Calculate Age from a date

Below, you will see the DimCustomer table, which can be found in the AdventureWorksDW table, which includes an entry for birthdates. I've removed the columns that don't need to be to make it easier to understand;

In order to calculate the actual average age of every buyer, all you need to do is:

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

That's it. It will calculate the amount which is the sum of the Birthdate column, as well as the time and date column.

The age, however, as it appears within the Age column, doesn't really appear to be an age. This is due to the fact that it's an actual Duration.

Duration

Duration is a distinctive type of data type within Power Query which represents the variations of the two DateTime values. Duration is a mixture 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 expected of them to comprehend the details like that. there are ways that you are able to obtain every part of the duration. When you select the Duration menu you'll notice that you can determine the number of seconds and minutes, hours, years and days from it.

For help calculating the age in years through an example, it is easy to select Total Years:

The duration is measured in days . It was then divided in 365days to yield the value of the year.

Rounding

The bottom line is that nobody says your age is 53.813698630136983! They use 53 which is the number rounded down. You can select Rounding and Round Down under the Transform tab for it.

This will reveal how old you are:

You can then clean the other columns if you'd like (or there could be that you made use of transformations within the Transform tab to prevent the creation of new columns) This column can be named column Age:

Things to Know

• Refresh The data's age determined in this manner will be refreshed during the time of refreshing your database. Every time the system is competent to match the birthdate to the date and timing of the refreshing. In this method can be described as an algorithm to calculate the age. If you'd like to have the calculation to be performed dynamically with DAX this is the method I have described the method I would recommend making use of.
• The reason behind Power Query: Benefits from using age calculation in Power Query is that the calculation is made when you refresh your report. This is made possible by an instrument that makes calculation significantly faster and easier, as well as there is no cost when calculating it with DAX because it is a way to gauge of runtime.
• Other scenarios They aren't intended for the calculation of age from the birthdate. It can be used to calculate the age of inventory for products and also the various 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 holds a BSc with a concentration in Computer engineering. There are more than 20 years work experience in the field of data analysis as well as databases, BI designing, and programming primarily using Microsoft technologies. He was a Microsoft Data Platform MVP for nine years in a row (from 2011, to now) because of his love for Microsoft BI. Reza is also a prolific writer and co-founder at RADACAD. Reza is also the co-founder and co-organizer for 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 different books. He was also an active forum member on online technical forums like MSDN as well as Experts-Exchange and was moderator for MSDN SQL Server Forums and holds the MCP and an MCSE as well as an MCITP in BI. He also is the leader of the New Zealand Business Intelligence users group. This group is also the writer of the book extremely well-loved Power BI from Rookie to Rock Star, which is entirely free and comprises nearly 1700 pages of content and an additional book called Power BI Pro Architecture published by Apress.
It is an International Presenter 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 aim is to assist users discover the most effective solutions for data, and he's an avid Data enthusiast.This blog post was posted into Power BI, Power BI from Rookie to Rockstar, Power Query and is filed under Power BI, Power BI from Rookie to Rock Star, Power Query. The following is an excellent resource to save to your bookmarks.

Post navigation

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