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 widely used language usedin several calculationsin Power BI a lot of people are unaware of the function that is available in Power Query. In this blog article, I will demonstrate how easy it is to calculateAge in Power BI using PowerBI. This methodis extremely helpful in cases where the computation of an agecan be performed using an earlier calculated row by row basis.

Calculate Age from a date

Here is the DimCustomer table that is part of the AdventureWorksDW table which contains an age column. I've removed a few columns that don't need to be there to make it more user-friendly;

To calculate the age of each buyer, all you need to do is:

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

It's that simple. it. this calculates the calculate the amount which is the total that is the sum of Birthdate column, and also the current date and time.

However, the date that is displayed in the Age column does not really appear like an age. That is because it is an actual duration.

Duration

Duration is a different type of data which is used by Power Query which represents the different between the two DateTime values. Duration is a combination of four numbers:

days.hours.minutes.seconds

This is how you see the above statements. But, from individuals' perspectives, you don't wish them to research information like this. There are ways to get each portion that is an amount of time. When you go to the Duration menu , you'll find that you can extract the amount of seconds or minutes of hours, days, or years out of it.

To help with calculating the age in years such as, for instance it is easy to select Total Years.

Note that the length of this program measured in days , and then divided into 365 days to provide you with the annual figure.

Rounding

Finally, no one says they are 53.813698630136983! They refer to it as 53, then they round it down. It's easy to select Rounding or Round Down under the Transform tab.

This will show you your age in years:

It's also possible to make other columns cleaner if you'd like (or perhaps you've used the power of transformations on the Transform tab to stop the development of new columns) This column may be renamed as the Age column and Age:

Things to Know

• Refresh The age calculated in this way will be updated each time you refresh your database. Each time, it will compare the birthdate with the date and time at which the refreshed. This method is a pre-calculation of the age. If you need the calculation to be performed dynamically by DAX, I have explained a way that is possible to use.
• The reasoning behind Power Query: Benefits from performing calculations for age in Power Query is that the calculation is done in the course of refreshing your report, using an instrument that makes the calculation much easier, and there's no additional cost to doing it using DAX for calculating the runtime.
• Another case where these are not meant to calculate age but rather start at the date of birth. This can be used to determine the inventory age for items, as well as the distinction from two days or months from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc from Computer engineering. He has an extensive 20-year' expertise in the field of data analysis as well as BI, databases development and programming with a focus on Microsoft technologies. He was a Microsoft Data Platform MVP for nine consecutive years (from 2011 until today) due to his dedication to Microsoft BI. Reza is an active writer and co-founder at RADACAD. Reza is also co-founder as well as co-organizer at the Difinity Conference which is held 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 has written a couple of ebooks on MS SQL BI and also is working on a few more. He was also a regular participant on technical forums online like MSDN or Experts-Exchange and was also the moderator for MSDN SQL Server forums, and is an MCP and MCSE as well as the MCITP for Business Intelligence. He is the creator of the New Zealand Business Intelligence users group. Also, the author of the popular publication Power BI from Rookie to Rock Star, which is completely free and includes more than 170 pages of content as well as it is a element of Power BI Pro Architecture published by Apress.
It is an International Speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's love is helping users find the best information solution. He is Data enthusiast.This article was published in Power BI, Power BI from Rookie to Rockstar, Power Query and is listed with Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

Share various visual pages by different security groups within Power BIAge Calculation in Years which is able by Power BI to calculate Leap Year in Power BI by using Power Query