Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that is a quick and easy method of calculating the age. However, because DAX is the most used language usedin several calculationsin Power BI, a lot of people do not know about the feature available in Power Query. In this blog post, I'll detail how simple it is to calculateAge using Power BI with PowerBI. It is a methodis very helpful in situations where it is required to do the age calculation.can be done using an earlier calculated row by row basis.

Calculate Age from a date

That's what's in this is the DimCustomer Table, that is an AdventureWorksDW table which functions in the capacity of the date-of-birth column. I've eliminated columns that don't need to be there in order to make it more clear;

To calculate the age of each buyer, you need is:

• In Power BI Desktop, Click on Transform Data
• In the PowerQuery Editor window, be sure that you select the column titled Birthdate first.
• Go to the add Column Tab, which is in the "From Date & Time" section. And under Date, choose Age

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

However, the age you see when you look at the Age column, doesn't seem like an age. It's because it's a real duration.

Duration

Duration is a special kind of data utilized to calculate the duration of a query in Power Query which represents the distinction between the two DateTime values. Duration is a mixture of four different values:

days.hours.minutes.seconds

That's what you'll find in the above information. But from a user's view, they shouldn't need to read details like the ones above. There are many ways to retrieve each component of the time. When you use the Duration menu there is a way that you can extract the quantity of minutes, seconds, hours, days , and years from it.

To aid in calculating the age in years for instance , it's easy to find the Total Year:

Be aware that the duration of the event is measured in days and then divided into 365 days to give the annual amount.

Rounding

The truth is that no one has stated the age of their child as 53.813698630136983! They are saying 53, which is an rounded down. It's easy to select the Rounding and then round down in the Transform tab.

This will give you the number of years:

It's then possible to clean other columns if you'd like (or there is a chance that you've applied transformations in the Transform tab to stop creating new columns) This column is then referred to as column; Age:

Things to Know

• Refresh The age calculated in this manner will be modified at the time of refreshing your database. Every time, it will be matched to the birthdate to the date of the day and the time the refresh was made. This method is a way to calculate earlier of age. If you'd like the calculation to be dynamically performed through DAX This is the method I've shown how to utilize.
• The reason behind Power Query: Benefits of calculating an age using Power Query is that the calculation is carried out after you refresh the report. It is done using an algorithm that makes the calculation more easy, and there will not be extra overhead in calculating it with DAX because it is a way to gauge runtime.
• Different scenarios. This isn't meant to be used for calculation of the age of a person based on their birth date. It could be used to determine product inventory and distinct dates and dates from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He earned a BSc at the University of Computer engineering. He has more than 20 years of experience in data analysis , programming, databases, BI and development generally using Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years in a row (from 2011 to the present) due to his dedication to Microsoft BI. Reza is a frequent blog author, and is the Editor and Co-founder for RADACAD. Reza is also co-founder and the co-organizer of Difinity the conference which takes place within 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 number of books about MS SQL BI and also is working on other books. He was also a frequent member of online forums on technical matters, like MSDN and Experts-Exchange and was moderator for the MSDN SQL Server forums, and is an MCP and MCS as well as an MCITP in Business Intelligence. He was the creator of the New Zealand Business Intelligence users group. In addition, he's also the author of the very popular book Power BI from Rookie to Rock Star, which is accessible for free and contains more than 700 pages of content as well as an edition of the Power BI Pro Architecture published by Apress.
This speaker has been an International speaker in 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 desire is to assist you discover the right solutions to data issues, and he's an avid Data enthusiast.This post was published under 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 good source to bookmark.

Post navigation

Share Different Visual Pages with different security groups in Power B.I.A.'s Calculation of Years that is used to calculate Leap Year in Power BI by making use of Power Query