Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way that is a simple method that is a simple method of calculating the age. However, because DAX is the most used language usedin several calculationsin Power BI, a lot of people don't have any idea about the feature available in Power Query. In this blog article I'll detail how simple it is to calculateAge for PowerBI using PowerBI. It is a methodis very useful when you have to perform the age calculation. Itcan be done using an earlier calculated row-by-row basis.

Calculate Age from a date

Here is what's known as the DimCustomer table , which comes part of the AdventureWorksDW table that serves as the column for birth dates. I've removed columns that don't need to be there to make it easier to read;

For you to calculate the age of each client, you'll need:

• In Power BI Desktop, Click on Transform Data
• In Power Query Editor window, be sure to choose the column titled Birthdate first.
• You can go to the add Column Tab that is found under the "From Date & Time" section. Under Date, select Age

That's about it. This will calculate any differences to your Birthdate column, and the current date and date.

But, the age is displayed when you look into the Age column doesn't appear like an actual age. It's due to the fact that it's a Duration.

Duration

Duration is a specific type of data that is used within Power Query which represents the different between the two DateTime values. Duration is a mixture of four different values:

days.hours.minutes.seconds

and that's what you'll find in the above information. But from a user's view, they shouldn't need to read information in the same way as those mentioned above. There are methods you can use to retrieve each component of the time. When you use the Duration menu you'll see that you can pull the total amount of minutes, seconds, hours, days , and years from it.

To aid in calculating the age in years for instance , it is simple to calculate the total Year:

Note that the duration is measured in days and was later divided in 365 hours to produce the annual sum.

Rounding

It's the truth, nobody declares the age of their child as 53.813698630136983! They use 53 which is an rounded down. It's easy to select the Rounding as an option and then turn down in the Transform tab.

This will provide you with the year-old number:

You can then purify other columns if like (or there's a chance that you've used transformations through the Transform tab in order to not create new columns) Then, you can name this column; Age:

Things to Know

• Refresh The age calculated this way will be changed when refreshing your data. Every every time it is refreshed, it'll be matched to the birthdate with the date of the day and the time the refresh of the database took place. This method is a way to calculate earlier of age. If you'd like your age calculation to be performed dynamically using DAX This is the method I've shown you how to use.
• The motive for using Power Query: Benefits of performing an age calculation by using Power Query is that the calculation occurs every time you refresh your report. It's done with an algorithm that makes the calculation more easy to do, and there won't an additional cost in calculating it with DAX in order to determine runtime.
• Different scenarios. This isn't intended to be used to calculate the age of a person based on their birth date. This can be used to calculate inventory of products and date and time that differ from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has earned an BSc with a major in Computer engineering. He has more than 20 years of experience in data analysis , the BI and databases fields, as well as programming and development primarily with Microsoft technologies. He was an Microsoft Data Platform MVP for nine consecutive years (from 2011, until now) because of his commitment to Microsoft BI. Reza is a frequent blog writer, and also the founding editor and founder of RADACAD. Reza is also co-founder as well as organizer of Difinity the conference that is held at 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 several books on MS SQL BI and also is working on other books. He also was a regular participant in online forums for technical issues like MSDN and Experts-Exchange and was the moderator for the MSDN SQL Server forums, and is an MCP and Microsoft Certified Specialist (MCSE) as well an MCITP in Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. In addition, he's also the author of very popular publication Power BI from Rookie to Rock Star, which is freely available and contains more than 17000 pages of information and it's the 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 users ' groups. And He is a Microsoft Certified Trainer.
Reza's desire is to assist you find the ideal solutions to data issues, and he's an avid Data enthusiast.This entry was filed 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. This is a fantastic resource for you to bookmark.

Post navigation

Share different visual pages with different Security Groups in Power The BIAge's Calculation of Years that is used to calculate Leap Year in Power BI by using Power Query