Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that's a basic method for calculating the age. However, because DAX is the most used language usedin several calculationsin Power BI, many are not aware of the functions available in Power Query. In this blog article , I'll discuss how easy it is to calculateAge with Power BI by using PowerBI. This methodis very beneficial when it is necessary to calculate the calculation of age.can be calculated from an earlier calculated row by row basis.

Calculate Age from a date

The table is called the DimCustomer table , and it comes taken from the AdventureWorksDW table that functions as the birthdate column. I've removed columns that aren't necessary in order to make it easier to read;

To calculate how old you are for each buyer, the following information is required:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window, make sure to select the column titled Birthdate first.
• Click on the Add Column Tab that is in the "From Date & Time" section, and under Date, select Age

That's all there is to it. This is how you calculate any change within your Birthdate column, and the current date and time.

But, the age appears when you look through the Age column, doesn't look like an actual age. This is because it's actually time period.

Duration

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

days.hours.minutes.seconds

This is what you'll discover in the details above. But from a user's standpoint, they shouldn't have to study details like the ones above. There are methods to retrieve each component of the time. By using the Duration menu, you will see that you can get the number of seconds, minutes, hours days and years out of it.

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

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

Rounding

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

This will provide you with the number of years in years:

It's also possible to clean other columns if you'd like (or there is a chance that you've used transformations through the Transform tab in order to not create new columns) And then name this column"Age".

Things to Know

• Refresh The age calculated in this manner will be altered when refreshing your database. Each each time it'll be matched to the birthdate to the date and date that the refresh was made. This method is a way to calculate earlier of age. If, however, you wish for the age calculation to be performed dynamically, using DAX This is exactly what I've shown you how to utilize.
• The reasoning behind Power Query: Benefits of performing an age calculation by using Power Query is that the calculation is carried out whenever you refresh your report. It's done with an algorithm that makes the calculation simpleand less complicated, so there won't be additional work involved in doing the age using DAX because it is a way to gauge runtime.
• Different scenarios This is not meant to be used for calculation of age by birthdate. This could be used to calculate product inventory age and the various dates and dates 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 a BSc at the University of Computer engineering. He have more that 20 years of experience in data analysis and BI, databases, programming and development primarily employing Microsoft technologies. He has been an Microsoft Data Platform MVP for nine consecutive years (from 2011 to the present) due to his dedication to Microsoft BI. Reza is a regular blog writer, and also the co-founder and editor of RADACAD. Reza is also co-founder and coordinator of Difinity the conference that 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 several books on MS SQL BI and also is writing other books. He was also a frequent member of online forums on technical issues such as MSDN and Experts-Exchange and was moderator on the MSDN SQL Server forums, as well as an MCP and the MCSE, as well as an MCITP in BI. He is the founder of the New Zealand Business Intelligence users group. He's also the author of very popular the book Power BI from Rookie to Rock Star, which is accessible for free and contains more than 17000 pages of information and it's the Power BI Pro Architecture published by Apress.
Speakers are 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 goal is to help you find the best solutions for data. He's a Data enthusiast.This entry was posted as 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 to bookmark.

Post navigation

Share various visual pages using different security groups in Power to use BIAge's Calculation of Years that is used for Leap Year in Power BI by using Power Query