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, as DAX is the largest and most widely used language usedin several calculationsin Power BI, many are unaware of the functions available in Power Query. In this article, I'll describe how simple you can calculateAge for PowerBI with PowerBI. This methodis very useful when you have for the age calculation.can be calculated using an earlier calculated row by row basis.

Calculate Age from a date

It's the DimCustomer table . This is in the AdventureWorksDW table that functions as the column for birth dates. I've removed columns that weren't needed to make it easier to read;

For you to calculate your age range for each purchaser, you must have:

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

That's all there is to it. This is how you calculate any variation within both the Birthdate column, as well as the current date and hour.

The age is displayed when you look through the Age column, doesn't look like an age. It's because it's an actual time period.

Duration

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

days.hours.minutes.seconds

And that's exactly what you'll discover in the details above. However, from a users' point of view, they shouldn't be required to look up details like the ones above. There are methods you can use to extract each part of the time. When you use the Duration menu it will show you how you can extract the quantity of minutes, seconds, hours, weeks and years from it.

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

Note that the duration is measured in days and then divided into 365 days for the year to get the total.

Rounding

In reality, nobody declares the age of their child as 53.813698630136983! The number is 53. This is reduced to. It's simple to select the Rounding option , and then to round down to the Transform tab for it.

This will give you the number in years:

It's also possible to purify other columns if like (or there's a chance that you've used transformations using the Transform tab to avoid creating new columns) Then, you can name this column"Age"

Things to Know

• Refresh The age that is calculated in this way will be modified at the time of refreshing your data. Every every time it is refreshed, it'll match the birthdate with the date and time that the data refresh occurred. This method is an earlier calculation of age. However, if you want the age calculation to be conducted dynamically using DAX This is what I've described the best way to use DAX.
• The rationale behind Power Query: Benefits of performing an age calculation with Power Query is that the calculation takes place after you refresh the report. It is done using an algorithm that makes the calculation simple, and there won't be extra overhead in calculating the age using DAX in order to determine runtime.
• Different scenarios. This isn't meant to be used for calculation of age by birthdate. This could be used in conjunction with inventory age of the product and various dates and dates that differ from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has an BSc at the University of Computer engineering. He has more than 20 years of experience in data analysis as well as databases, BI, programming and development generally making use of Microsoft technologies. He has been 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 director and co-founder of RADACAD. Reza is also the co-founder, as well as the co-organizer 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 working on other books. He also was a regular member of online forums on technical matters, like MSDN and Experts-Exchange, and was moderator for the MSDN SQL Server forums, as well as an MCP and MCSE as well as an MCITP in Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. In addition, he's the author of the highly acclaimed workbook Power BI from Rookie to Rock Star, which is available for download for free and includes more than 700 pages of content as well as it's the Power BI Pro Architecture published by Apress.
The speaker is 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 dream is to help users find the most effective solutions to data issues, and he's an avid Data enthusiast.This entry was posted on the topic of 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 an excellent 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 for Leap Year in Power BI by using Power Query