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 are unaware of this feature of Power Query. In this article, I'll describe how easy to calculateAge in Power BI using PowerBI. It's a methodis very efficient in situations where your estimation of agecan be performed using a pre-calculated row rows basis.

Calculate Age from a date

Below, you'll find the DimCustomer table, which is part of the AdventureWorksDW table that has an entry for birthdates. I've removed some of the columns that aren't needed for ease of look at;

In order to calculate the the age for each purchaser all you need to do is:

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

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

However, the age that appears as a number in the Age column, however, doesn't seem to be an actual age. It's due to an actual Duration.

Duration

Duration is a unique kind of data format in Power Query which represents the variations of the two DateTime values. Duration is a mix of four different values:

days.hours.minutes.seconds

and that's how you see the numbers above. From the perspective of the user it is not their responsibility to comprehend the details like that. there are ways that you can get every portion of the duration. Utilizing the Duration menu option, there is a way to get the duration in seconds as well as minutes, hours months and days from it.

To assist in calculating the age in years through an example, it is easy to click on Total Years:

The duration is measured in days . It was then divided in 365 to provide the annual amount.

Rounding

It's the truth, nobody declares you old in 53.813698630136983! The number is 53. This is reduced to a lower number. It is possible to select Rounding and Round Down by clicking on the Transform tab for it.

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

You can then cleanse the other columns if you'd like (or there could be that you applied transformations within the Transform tab, to prevent creating new columns) It is possible to name this column"Age:

Things to Know

• Refresh The information's age calculated through this method will be refreshed each time you refresh your database. Each time the system will be capable of comparing the birth date to the date and the time during the process of refreshing. In this method involves an algorithm used to determine the age. If you'd like to have the calculation of age to be executed dynamically by using DAX here is how I have described the method I would recommend making use of.
• The main reason for Power Query: Benefits of using the age calculation feature in Power Query is that the calculation takes place during the process of refreshing your report. This is done by using an instrument that makes calculation much easier and faster, and there's no added cost to calculate it using DAX because it is a way to gauge runtime.
• Other scenarios These are not meant to be used for calculation of the date of birth. 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 a BSc with a major in Computer engineering. There are more than 20 years work experience in the field of data analysis data, BI, databases designing, and programming primarily using Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years in a row (from 2011, to now) for his passion for Microsoft BI. Reza has been a prolific author and co-founder of RADACAD. Reza is also co-founder and co-organizer of 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 wrote some books on MS SQL BI and also is writing different books. He was also a regular participant in online forums for technical issues like MSDN as well as Experts-Exchange and was moderator for MSDN SQL Server forum and holds the MCP, MCSE and as an MCITP for BI. He also serves as the leader of the New Zealand Business Intelligence users group. The group is also creator of the book highly praised Power BI from Rookie to Rock Star, which is absolutely free and contains more than 1700 pages of material along with a separate 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 ambition is to help people find the best solutions for data, and Reza is an avid Data enthusiast.This post was posted 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. The following is an excellent resource for you to bookmark.

Post navigation

Share Different Visual Pages with different Security Groups Power BIAge in Years Calculation , which works for Leap Year in Power BI by using Power Query