Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that is a quick and easy method of calculating the age. However, since DAX is the most used language usedin several calculationsin Power BI, a lot of people do not know about the feature available in Power Query. In this blog post, I'll explain how easy it is to calculateAge in Power BI using PowerBI. This methodis very useful when it is necessary to perform the age calculation.can be done from an earlier calculated row by row basis.

Calculate Age from a date

The table is called this is the DimCustomer table , which comes in the AdventureWorksDW table which functions as the birthdate column. I've removed the columns that aren't needed to make it more readable;

For you to calculate how old you are for each customer, you will need:

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

That's all there is to it. This calculates the calculate any variation in your Birthdate column, as well as the current date and date.

The age you can see when looking into the Age column doesn't appear like an actual age. It's because it's not a time period.

Duration

Duration is a particular kind of data utilized to calculate the duration of a query in Power Query which represents the variation between the two DateTime values. Duration is a combination of four different values:

days.hours.minutes.seconds

This is what you'll read in the data above. However, from users' standpoint, they shouldn't have to go and read the information in the same way as those mentioned above. There are ways you can locate every component of time. If you select the Duration menu you will see that you can extract the amount of minutes, seconds, hours, days , and even years from it.

To assist in calculating the age in years such as it is possible to get to the Total Year:

Note that the duration is measured in days . It was then divided by 365 to yield the yearly amount.

Rounding

The truth is that no one has stated what their child's age as 53.813698630136983! They say 53, which is an rounded down. It's easy to choose the Rounding, then click round down by selecting the Transform tab.

This will give you the year-old number:

It is then possible to clean other columns if you'd like (or there's a chance that you've applied 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 manner will be altered when refreshing your data. Each time, it will be matched to the birthdate to the date and day that the refresh of data took place. This method is a way to calculate earlier of age. If, however, you would like the calculation of age to be done dynamically with DAX This is the method I've shown you how you can make use of.
• The rationale for using Power Query: Benefits of performing an age calculation with Power Query is that the calculation is carried out whenever you refresh your report. It's done with an algorithm which makes the calculation simple, and there won't an additional cost in calculating it using DAX to measure runtime.
• Different scenarios This is not designed to calculate age by birthdate. This can be used to calculate inventory age of the product and different 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 earned a BSc in Computer engineering. He has more than 20 years of expertise in data analysis, databases, BI, programming and development, mostly using Microsoft technologies. He was an Microsoft Data Platform MVP for nine consecutive years (from 2011 until today) because of his commitment to Microsoft BI. Reza is a regular blog writer, and 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 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 has written several books on MS SQL BI and also is working on other books. He was also a frequent participant in online forums for technical issues , such as MSDN and Experts-Exchange as well as moderator on the MSDN SQL Server forums, and is an MCP and MCSE as well as an MCITP in BI. He is the founder of the New Zealand Business Intelligence users group. In addition, he's the author of very popular workbook Power BI from Rookie to Rock Star, which is accessible for free and contains more that 1700 pages of content and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
It is an International speaker of 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 ambition is to help you find the best solutions to data issues, and he's an avid Data enthusiast.This entry was posted 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. The following is a great resource for you to bookmark.

Post navigation

Create Different Visual Pages by 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