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 commonly used language usedin several calculationin Power BI, many are unaware of this feature of Power Query. In this post, I will show you how simple it is to calculateAge in Power BI by using Power BI. This methodis extremely beneficial for situations in which the computation of an agecan be conducted on a previous calculated row by row basis.

Calculate Age from a date

That's the DimCustomer table from the AdventureWorksDW table, which includes a birthdate column. I've taken out some columns that aren't required to make it more readable;

To calculate what is the average age each buyer, you must have:

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

That's all there is to it. This will calculate the amount which is the sum of the Birthdate column, and also the current date and time.

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

Duration

Duration is a unique type of data which is used for Power Query which represents the differences between two DateTime values. Duration is a combination of four values:

days.hours.minutes.seconds

This is how you view the above data. But from an individual's perspective you don't want them to find information like this. There are methods that can make each of the portions that are an amount of time. When you click on the Duration menu , you'll be able to see that you'll be able to get the amount of seconds , minutes as well as days, hours and years out of it.

In order to aid in calculating the age in years like, for example, it is simple to choose Total Years.

Make note of the fact that the duration of the program is measured in days and afterwards divided into 365 in order to provide you with the annual cost.

Rounding

Finally, no one says they are 53.813698630136983! They use the term 53, and then they round it down. It's easy to select Rounding and Round Down on the Transform tab.

This will give you the number in years:

It is then possible to remove other columns if you'd like (or perhaps you've taken advantage of transformations within the Transform tab to stop the creation of new columns) This column may be renamed as Age: column (Age:

Things to Know

• Refresh The age that is calculated using this method will be updated every time you are refreshing your database. and each time will compare the birthdate to the date and date when the update was completed. This method is a pre-calculation of age. If you're looking for calculations to be run dynamically using DAX, here I presented a method is possible to use.
• The rationale behind Power Query: Benefits of making age calculations with Power Query is that the calculation is done while you refresh the report. The report is refreshed using an application that makes the calculation more efficient, and there's no added cost of doing it with DAX to determine the runtime.
• Another option is to use these numbers to be used for age calculations, but only start with the birth date. This can be used to calculate the inventory age for items and also to determine the differences among two dates each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc from Computer engineering. He holds greater than twenty years of knowledge in the field of data analysis databases, BI and programming primarily using Microsoft technologies. He was a Microsoft Data Platform MVP for nine years in a row (from 2011 until today) for his dedication in the field of Microsoft BI. Reza can be found as an active author and co-founder of 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 also written a few publications regarding MS SQL BI and also is working on other books. He was also a regular forum member on technical forums on the internet like MSDN and Experts-Exchange and was the moderator for MSDN SQL Server forums, and holds the MCP or MCSE. He also holds the MCITP for BI. He is the creator of the New Zealand Business Intelligence users group. Additionally, he's the writer of the well-known workbook Power BI from Rookie to Rock Star, which is free and contains more than 170 pages of content as well as it is component 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 passion is to help people find the right data solution. He's a Data enthusiast.This entry was posted within 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. Bookmark the permalink.

Post navigation

Use different visual pages to share with various security groups inside Power BIAge in Years Calculation which can be used to calculate Leap Year in Power BI with the help of Power Query