Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. However, as DAX is the main language usedin numerous computationsin Power BI, many are unaware of this feature in Power Query. In this article I'll explain how easy it is to calculateAge within Power BI by using Power BI. The methodis extremely beneficial in cases where your estimate of the agecan be calculated using an earlier calculated row-by-row row basis.

Calculate Age from a date

Below you will find the DimCustomer table that is comprised of AdventureWorksDW table which functions as an entry point for the birthday column. I've taken out a few of the columns in order so that it is easier look through.

To calculate your age for each client All you have to do is to:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window, choose the first column for the Birthdate column.
• Go to the Add Column Tab after which choose"Add column tab," and click the "From Date & Time" section. In the Date, select the age range.

That's it. this is how you calculate an amount that is the sum of the Birthdate column, as well as the current date and hour.

But, the age that appears in the Age column, but it doesn't appear to be a true age. It's because it's actually a length.

Duration

Duration is a special type of data in Power Query which represents the variation between the two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

This is the way to take the information above into consideration. From the perspective of the user it is not their responsibility to read the full details of this. There are methods to extract every bit of information from the time. Selecting from the menu of Duration, you'll see that you are able to take the number of seconds and minutes along with days, hours and years out of it.

To assist with calculating the age in years such as it is easy to hit the Total Year:

The duration is measured in days . Then it is was divided by the days, to calculate the annual amount.

Rounding

The truth is that no one says they're 53.813698630136983! They claim 53, which is reduced to a round number. It's simple to choose Rounding and round down from the Transform tab.

This will let you know what your old age is in terms of years.

You can then clean the other columns, if desired (or maybe you've taken advantage of transformations with the Transform tab to not have inventing new columns) You can name this column Age:

Things to Know

• Refresh The age that is calculated in this manner is refreshed every time you're refreshing your information. And each time it will be comparing the date of birth to the date and duration at the time of refresh. This method is a pre-calculation for an age. If you require the calculation be performed dynamically by DAX here's the method I used to describe the method you can apply.
• The motive behind Power Query: Benefits of performing an age calculation with Power Query is that the calculation takes place in the course of refreshing the report. The report is refreshed using an instrument which makes the calculation simpler and faster, plus there won't be extra overhead when it is calculated using DAX for measuring runtime.
• Alternative scenarios It's not used to calculate the age of a person based on their birth date. This is a good way to determine inventory-level ages for inventory items and for the variations between two dates or times 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 on Computer engineering; he is more than 20 years old. experience in the field of data analysis and database programming, BI and development with a focus on Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine years in a row (from 2011 until the present) in recognition of his passion of Microsoft BI. Reza is a prolific writer for blogs and co-founder and director of RADACAD. Reza is also co-founder and co-organizer of Difinity Conference. 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 also wrote a few books about MS SQL BI and also is working on more books. He also was a regular participant in online forums for technical issues such as MSDN and Experts-Exchange and was also moderator of the MSDN SQL Server forums, and is an MCP and MCSE and an MCITP of Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. He is also the co-author of the well-known Book Power BI from Rookie to Rock Star, which is free and has more than 1800 pages of material and It is also the author of 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 Users Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to assist users find the perfect data solution. He is a Data enthusiast.This post was written by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is categorized in 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 Multiple Visual Pages with different Security Groups within Power BIAge's Calculation of Years which is applicable to Leap Year in Power BI by using Power Query