Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. But, since DAX is the largest and most well-known language usedin numerous calculationsin Power BI, many do not realize this feature within Power Query. In this blog post , I will discuss how easy for you to calculateAge in Power BI and Power BI. This methodis highly beneficial for situations in which you require be able to calculate the calculation of age.can be done using a calculated row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table that's one of AdventureWorksDW table, which is the birthdate column. I've removed a few columns that aren't required to make it more readable;

To calculate the approximate age of each client is, all you must do is to:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; select the column that is the primary one, Birthdate.
• To add a column, click on the Column Tab and then click on the "From Date & Time" section, and then under Date select the age range.

That's all there is to it. This is the method to calculate what's the proportion between the birthdate column as well for the current date as well as time.

However, the date that appears on the Age column doesn't actually appear to be an actual age. This is because it's not an actual time period.

Duration

Duration is a specific type of data that is utilized in Power Query which represents the differences in Two DateTime values. Duration is a combination of four numbers:

days.hours.minutes.seconds

This is how you comprehend the above figures. But from the standpoint of the user, it's not required of them to read specifics like this. There are methods to get each portion of the time. By selecting the Duration menu you'll see the amount of seconds, minutes, hours as well as years.

In order to aid with calculating the age in years for example, it is easy to select Total Years.

It is vital to keep in mind it is that durations are measured as days and after that divided by number of days, to give you the annual value.

Rounding

Final note: nobody claims that the age of their child is 53.813698630136983! They claim 53, which is rounding down. It's easy to select Rounding and then round down option in the Transform tab for it.

This will tell you the age of the years:

Then, you can clean the other columns in the event that you'd like (or perhaps it's that you used transformations on the Transform tab to prevent creating new columns) This column can be renamed to Age: column Age.

Things to Know

• Refresh The age calculated in this manner will be updated whenever you refresh your data. Every time, it will match the date of birth with the date and the time that the data refresh occurred. This method is an algorithm for pre-calculating age. If you want the calculation of the age to be carried out dynamically using DAX Here's a description of the method to apply.
• What is the reason? Power Query The benefit of using the calculation of age using the Power Query is that the calculation is performed while you refresh your report. It is done using an application that facilitates the calculation, and there is no extra cost to calculate it using DAX in the measurement of runtime.
• Other scenarios There isn't an effective method to calculate age solely on the basis of birthdate. This can be used to calculate the stock age for products and also the distinctions between two dates and times that are different from one.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds an BSc of Computer Engineering. He has more than 20 years' worth of experience in the field of data analysis and BI, databases, programmingand development, mainly using Microsoft technologies. Reza has been an official Microsoft Data Platform MVP for nine years in a row (from 2011, until today) for his dedication to Microsoft BI. Reza is a prolific writer and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity Conference 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 has written a few publications regarding MS SQL BI and also is writing other books. He was also a regular user of forums for technical issues like MSDN as well as Experts-Exchange and was moderator on the MSDN SQL Server forum and holds the MCP and MCSE , as well as MCITP in BI. He is the founder of the New Zealand Business Intelligence users group. In addition, he's also the author of the widely popular publication Power BI from Rookie to Rock Star, which is free and offers greater than 17,000 pages of content and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
This speaker has been an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday as well as SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's purpose is to help you to find the most effective data solution. He is an avid Data enthusiast.This blog post was posted by Reza on Power BI, Power BI from Rookie to Rockstar, Power Query and was tagged as Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

Share visual pages on various security groups within the Power BIAge's Year Calculation that is used to calculate the Leap Year in Power BI by using Power Query