Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that's a straightforward method to calculating the age. However, as DAX is the largest and most used language usedin several calculationsin Power BI, many don't have any idea about the feature available in Power Query. In this blog article I'll show you how easy it can be to calculateAge for PowerBI with PowerBI. It is a methodis extremely useful when it is necessary to do the age calculationcan be done by calculating the age on a row by row basis.

Calculate Age from a date

That's what's in what's known as the DimCustomer table . It comes part of the AdventureWorksDW table that functions in the capacity of the date-of-birth column. I've eliminated columns that aren't needed to make it easier to read;

To calculate your age each buyer, the following information is required:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window, ensure that you select the column titled Birthdate first.
• Go to the add Column Tab and click on the Add Column Tab, which is beneath the "From Date & Time" section. Then, under Date, choose Age

That's all there is to it. The calculation will calculate any variations in this column. Birthdate column, as well as the current date and time.

The age you see when you look on the Age column, does not appear like an age. It's because it's a real Duration.

Duration

Duration is a special kind of data used within Power Query which represents the differences between the two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

and that's what you will read in the data above. But, from the user's view, they shouldn't need to go and read the information as detailed in the above. There are many ways to find each element of the time. By using the Duration menu, you'll be able to see how you can take the value of minutes, seconds, hours, days , and even years from it.

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

The duration is measured in days and was then divided into 365 days to produce the annual sum.

Rounding

In reality, nobody states that their kid's age is 53.813698630136983! They state 53, which is rounded down. It's very easy to select Rounding option and then round down using the Transform tab for it.

This will give you the number in years:

Then, you can remove other columns if you'd like (or it is possible that you've used transformations using the Transform tab to avoid the need to create new columns) You can also name this column; Age:

Things to Know

• Refresh The age calculated in this manner will be changed at the time of refreshing your data. Each each time it'll match the birthdate with the date and date that the refresh was made. This method is an earlier calculation of age. If, however, you'd like the calculation to be conducted dynamically with DAX This is what I've described how to make use of.
• The motivation for using Power Query: Benefits of calculating an age using Power Query is that the calculation occurs whenever you refresh your report. It's done with an algorithm that makes the calculation more easy to do, and there won't be additional work involved in doing it using DAX for a measurement of runtime.
• Different scenarios This is not intended for the calculation of age by birthdate. This can be used to calculate inventory of products and date and time 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 a BSc with a major in Computer engineering. He has more than 20 years of expertise in data analysis, database programming, BI, and development mostly using Microsoft technologies. He was an Microsoft Data Platform MVP for nine years in a row (from 2011 until the present) for his commitment to Microsoft BI. Reza is a frequent blog writerand co-founder and editor of RADACAD. Reza is co-founder and the co-organizer of Difinity the conference, which is located in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote a variety of books on MS SQL BI and also is writing other books. He was also a regular participant in forums online for technical issues such as MSDN and Experts-Exchange as well as moderator for the MSDN SQL Server forums, and holds an MCP and MCSE as well as an MCITP in BI. He is the founder of the New Zealand Business Intelligence users group. Additionally, he's the author of very popular workbook Power BI from Rookie to Rock Star, which is available for download for free and includes more than 170 pages of content. It also contains The Power BI Pro Architecture published by Apress.
The speaker is an International Speaker on 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 perfect solution to data issues, and he's an avid Data enthusiast.This post was posted in 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 using Different Security Groups within Power the BIAge's Calculation of Years that is used for Leap Year in Power BI by using Power Query