Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. But, as DAX is the most popular language usedin numerous analysesin Power BI, many are unaware of this option within Power Query. In this blog post I'm going to show you how easy it is to calculateAge in Power BI and Power BI. The methodis extremely beneficial in situations where you need in order to calculate the estimated age.can be performed on an earlier calculated row-by-row row basis.

Calculate Age from a date

Here is the DimCustomer table, which forms part of the AdventureWorksDW table and is the column for birthdates. I've removed any extra column to make it simpler to read.

If you wish to calculate an age range for every client all you'll need to do is:

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

It's that simple. it. This is how you calculate any differences between the Birthdate column as well as the current date and time.

The age, however, which appears under"Age" but, since it is under the Age column, but doesn't appear to be an actual date. That is because it is an actual duration.

Duration

Duration is a specific type of data Data Type in Power Query which represents the different between the two DateTime values. Duration is a mix from four different numbers.

days.hours.minutes.seconds

This is the way you'll get the information above. However, from the user's point of view you shouldn't believe that they'll be able to understand specifics similar to the ones mentioned above. There are different ways to get each portion of the time. If you click on the Duration menu you will be able to take the duration of minutes and seconds, hours, days, and years from it.

To aid with calculating the age in years by example, click Total Year:

Note that the duration is calculated by days. It is after that divided by the number of days, so that you get the annual value.

Rounding

It's the truth, nobody claims to be 53.813698630136983! They call it 53, then rounded down. It is simple to choose Rounding and round down from the Transform tab.

This will tell you an age range in years:

Then you can tidy the other columns that you'd like to (or you could have used transformations from the Transform tab to avoid creating new columns.) You can name this column Age.

Things to Know

• Refresh: The age calculated in this manner will be updated when the time comes refresh of your data. Each time, it compares the date of birth with the date and time during the process of refreshing. This method is a more earlier calculation of the age. If you require that the calculation be made dynamically using DAX here's how I explained how to use.
• What is the reason? Power Query benefits of the age calculation feature of Power Query is the fact that the calculation happens each time you refresh your report. The calculation is carried out using an algorithm which makes the calculation easier and faster, and there is no additional overhead to calculate it using DAX to measure of runtime.
• Alternative scenarios used to calculate age only from the birthdate. This is a good way to determine the age of inventory of goods and also for the distinction among two dates and times one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc at the University of Computer engineering. He is more than twenty years of experience in the field of data analysis, database, BI, programming and development, mostly using Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine years in a row (from 2011 until now) because of his dedication for Microsoft BI. Reza is a prolific writer and co-founder for RADACAD. Reza is also the co-founder and co-organizer of the Difinity event in New Zealand and the Difinity conference 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 several books on MS SQL BI and also is working on a few more. He was also a regular forum participant on online forums for technical questions like MSDN and Experts-Exchange as well as moderator of MSDN SQL Server forums, and is a holder of an MCP, MCSE, and the MCITP for BI. He is the director of the New Zealand Business Intelligence users group. The group is also writer of the highly acclaimed volume Power BI from Rookie to Rock Star, which is completely free and offers over 700 pages of content in addition to The 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 in addition to SQL group for users. And He is a Microsoft Certified Trainer.
Reza's love is helping users find the best information solution. He is Data enthusiast.This post was published within Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great reference to save.

Post navigation

Use Different Visual Pages to Share by using Different Security Groups within Power BI's Year Calculation. This is used for Leap Year in Power BI through Power Query