Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, because DAX is the most popular language usedin many computationsin Power BI, many do not understand this capability of Power Query. In this blog , I'll describe how simple to calculateAge in Power BI with PowerBI. This methodis very efficient when the estimation of the agecan be performed on a pre-calculated row and on a row basis.

Calculate Age from a date

Below you can view the DimCustomer table that comprises the AdventureWorksDW table. It has the birthdate column. I've removed several of the columns which aren't needed in order to make it easier for you to comprehend;

To calculate the actual time of each customer's purchase, all you need to do is:

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

That's it. It will calculate an amount that is the total of the Birthdate column, along with the date and time.

However, the number of years on the Age column, but it doesn't appear to be an age. It's due to an actual duration.

Duration

Duration is a distinct type of data type within Power Query which represents the differentiating factors from the two DateTime values. Duration is a combination of four different values:

days.hours.minutes.seconds

That's the way you see the numbers above. But from the viewpoint of the user, it's not their responsibility to comprehend the details like that. There are ways you are able to get each part of the duration. By using the Duration menu, you'll be able to determine the number of seconds minutes, hours, years and days from it.

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

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

Rounding

Finally, no one proclaims their age in 53.813698630136983! They call it 53, which is round down. You can choose Rounding and Round Down by clicking on the Transform tab.

This will give you an indication of how old you are:

Then, you can clean the other columns if you'd like (or there could be because you made use of transformations in the Transform tab to prevent the creation of new columns) You can name this column: Age

Things to Know

• Refresh The data's age determined this way will be refreshed every time you refresh your database. Each time it is refreshed, the system will be competent to match the birthdate to the date and the date and time of refreshing. That method involves an algorithm to calculate the age. If you'd like to have the calculation to be done in a dynamic manner using DAX Here is the procedure I explained the best way to make use of.
• The motive behind Power Query: Benefits of using age calculations with Power Query is that the calculation is performed at the time of refreshing your report. This is done by using a tool that makes calculation simpler and faster and there's no added cost when calculating it with DAX to measure runtime.
• Other scenarios They aren't intended to be used to calculate age from the birthdate. It can be used to calculate the time of inventory on products and also the various dates and dates with respect to 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 degree in Computer engineering. There are more than 20 years experience in the field of data analysis as well as databases, BI designing, and programming primarily with Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years in a row (from 2011 until the present) because of his love for Microsoft BI. Reza is a prolific writer and co-founder at RADACAD. Reza is also co-organizer and co-founder 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 a few books on MS SQL BI and also is working on additional books. He was also a regular participant in online forums for technical issues like MSDN as well as Experts-Exchange and was moderator in the MSDN SQL Server forums and holds the MCP and MCSE , as well as an MCITP for BI. He is also the leader of the New Zealand Business Intelligence users group. They are also the authors of the book that is very well-loved Power BI from Rookie to Rock Star, which is totally free and comes with over 1700 pages of material and an additional book called Power BI Pro Architecture published by Apress.
It is an International Speaker in 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 goal is to help people find the best solutions to manage data. he's an avid Data enthusiast.This article was published within Power BI, Power BI from Rookie to Rockstar, Power Query and is listed under Power BI, Power BI from Rookie to Rock Star, Power Query. The following is an excellent site to bookmark.

Post navigation

Share Different Visual Pages with different Security Groups. PowerBIAge in Years Calculation that works with Leap Year in Power BI by using Power Query