Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that's a straightforward method in calculating the age. But, since DAX is the most popular language usedin several calculationsin Power BI, many don't know about the feature available in Power Query. In this blog , I'll show you how easy the process is calculateAge in Power BI with PowerBI. It is a methodis very helpful when it is necessary to carry out the age calculationcan be calculated by calculating the age on a row by row basis.

Calculate Age from a date

That's what's in the DimCustomer table , which is from the AdventureWorksDW table, which acts like the birthday date column. I've removed the columns that aren't needed for clarity;

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

• In Power BI Desktop, Click on Transform Data
• In Power Query Editor window, make sure to choose the first column, namely the Birthdate.
• Go to the add Column Tab and click on the Add Column Tab, which is found under the "From Date & Time" section. In the Date, choose Age

It's that simple. it. It will calculate any differences in both the Birthdate column, as well as the current date and hour.

But, the age you can see when looking on the Age column, doesn't seem like an actual age. It's because it's not a Duration.

Duration

Duration is a distinct type of data that is utilized for Power Query which represents the differences between two DateTime values. Duration can be described as a mixture of four different values:

days.hours.minutes.seconds

And that's exactly what you'll find in the above information. But from a user's view, they shouldn't need to look up specifics like the ones in the previous paragraphs. There are methods to retrieve each component of the time. When you use the Duration menu it will show you how you can get the number of minutes, seconds, hours, weeks and years from it.

To assist with calculating the age in years for instance , it is straightforward to calculate Total Year:

Be aware that the duration of the event is determined in days. It is then divided in 365 to calculate the year-long amount.

Rounding

Truthfully, no one identifies how old their baby is. 53.813698630136983! They say 53, which is an rounded down. It's easy to select the Rounding option , and then to round down on the Transform tab for it.

This will give you the years of experience:

It's also possible to tidy other columns as you'd like (or there's a possibility that you have applied transformations in the Transform tab to prevent the creation of new columns) This column is then referred to as column"Age"

Things to Know

• Refresh The age that is calculated in this manner will be changed when refreshing your data. Each subsequent time, it'll be matched to the birthdate to the date on which the data refresh took place. This method is a way to calculate earlier of age. If you would like the calculation of age to be conducted dynamically through DAX This is the approach I've described how you can make use of.
• The reason behind Power Query: Benefits of performing an age calculation with Power Query is that the calculation takes place every time you refresh your report. The calculation is performed using an algorithm that makes the calculation more simple, and there won't be extra overhead in calculating it with DAX because it is a way to gauge runtime.
• Different scenarios. This isn't intended for the calculation of age by birthdate. This could be used to calculate product inventory and distinct dates and dates from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He is a graduate of an BSc with a major in Computer engineering. He has more than 20 years of working in the field of data analysis and programming, databases, BI and development mostly making use of Microsoft technologies. He was a Microsoft Data Platform MVP for nine consecutive years (from 2011 until now) due to his dedication to Microsoft BI. Reza is a regular blog author, and is the co-founder and editor of RADACAD. Reza is also the co-founder, as well as co-organizer of Difinity the conference that is held 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 working on other books. He was also a frequent participant in online forums on technical matters, like MSDN and Experts-Exchange as well as moderator on the MSDN SQL Server forums, and is an MCP , MCSE as well as an MCITP in Business Intelligence. He was the creator of the New Zealand Business Intelligence users group. In addition, he's the author of very popular books Power BI from Rookie to Rock Star, which is available for free and has more that 1800 pages of material and an edition of the Power BI Pro Architecture published by Apress.
The speaker 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 aim is to help you find the best solutions for data, and he's a 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. This is a great resource to bookmark.

Post navigation

Share Different Visual Pages with Different Security Groups within Power BIAge's Calculation of Years that is used to calculate Leap Year in Power BI by using Power Query