Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. But, since DAX is the most used language usedin numerous computationsin Power BI, many are unaware of this function in Power Query. In this blog post I will demonstrate how simple it is to calculateAge within Power BI and Power BI. This methodis exceptionally beneficial in situations where you need for calculate the calculations of agecan be performed by calculating the row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table that is part of the AdventureWorksDW table, which is the birthdate column. I've removed some of the columns that aren't needed for clarity;

In order to calculate the approximate age of each client will be, all you'll have to do is to:

• In Power BI Desktop, Click on Transform Data
• In the PowerQuery Editor window select the column that is the primary one, birthdate.
• Select the Add Column Tab next to the "From Date & Time" section, then under Date Choose the appropriate age range.

That's all there is to it. This is how you calculate what is the ratio between the birthdate column as well in the current date and time.

But, the age appears on the Age column, but it doesn't appear to be an actual age. This is because it's not an actual length.

Duration

Duration is a special type of data that is used in Power Query which represents the variations in Two DateTime values. Duration is composed of four numbers:

days.hours.minutes.seconds

This is how you understand the above numbers. But from the perspective of the user it's not expected of them to read specifics like this. There are methods that can make each section of time. By selecting the Duration menu you'll get the total of seconds, minutes, hours months and days from it.

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

It is essential to know how the length is calculated in days and after that divided by the number of days so that you can get the value for the year.

Rounding

In the end, nobody declares that they are 53.813698630136983! They simply say 53. This is lower than the original. It's easy to select Rounding and then round down option in the Transform tab for it.

This will tell you the year of birth:

Then, you can tidy the other columns If you'd like (or you could find that you used transformations on the Transform tab to avoid creating new columns) The column may be named something like Age: column Age.

Things to Know

• Refresh The age that is calculated by this method will be updated as you refresh your data. Every time, it will match the date of birth to the date and date when the refresh took place. In this method will be an algorithm for pre-calculating age. If, however, you require the calculation of age to be carried out dynamically using DAX Here's how I explained the method that you can utilize.
• What's the explanation? Power Query: Benefits of using age calculation with The benefit of using age calculation with Query is that it is calculated at the time of refresh of your report. It's done with an application that facilitates the calculation, and there is no extra cost to calculate it using DAX in the measurement of runtime.
• Other scenarios It isn't the only method to calculate age solely on the basis of birthdate. This method can be used to calculate the age of inventory of products and also the differences between dates or times from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc of Computer Engineering. He has more that twenty years of experience in the area of data analysis, programming, databases, BI and development that is mainly based on Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine years in a row (from 2011, until today) for his dedication towards Microsoft BI. Reza can be described as an prolific author and co-founder of RADACAD. Reza is also the co-founder and co-organizer of the Difinity Conference located 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 some publications regarding MS SQL BI and also is working on other books. He was also a regular member of technical forums on the internet such as MSDN as well as Experts-Exchange and was the moderator for MSDN SQL Server forums as well as holding MCP and MCS as well as the MCITP for BI. He is the head for the New Zealand Business Intelligence users group. In addition, he's the author of the highly popular book Power BI from Rookie to Rock Star, which is free and contains greater than 17,000 pages of data and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
Speakers are 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 aim is to help you to find the most effective data solution. He is an avid Data enthusiast.This blog post was posted on Power BI, Power BI from Rookie to Rockstar, Power Query and was tagged as Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. The following entry was filed under Power BI.

Post navigation

Share visual pages on various security groups within PowerBIAge's Year Calculation that is used for Leap Year in Power BI using Power Query