Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. But, since DAX is one of the well-known language usedin numerous computationsin Power BI, many do not realize this feature of Power Query. In this blog post , I will explain how simple the process is calculateAge in PowerBI using PowerBI. The methodis exceptionally helpful in cases where you need in order to calculate the the calculations of age.can be carried out using a calculated row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table that is an element of the AdventureWorksDW table and is the birthdate column. I've removed a few columns that don't need to be there for clarity;

In order to calculate how old every client, the only thing you'll need to do is to:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window, select the first column. Birthdate.
• Click on the Add Column Tab next to the "From Date & Time" section. Then, under Date select the age range.

This is all there is to it. This is the method to calculate what's the ratio of the birthdate column, aswell as the current date and time.

However, the age that appears in the Age column isn't really believed to be an age. This is because it's an actual length.

Duration

Duration is a special type of data utilized to calculate the duration of Power Query which represents the differentiating factors between two DateTime values. Duration is a mix from four different numbers.

days.hours.minutes.seconds

This is the best way to read the above values. From the point of view of the user, it's not expected of them to be able to discern the meaning of these numbers. There are methods that can find each segment of time. By selecting the Duration menu you'll get the total of seconds, minutes, hours and years.

To assist to assist calculating the age in years for instance it is simple to choose Total Years.

It is crucial to remember that the duration is measured in days, and following that, divided by the number of days, in order to calculate the annual value.

Rounding

Final note: nobody says that your age is 53.813698630136983! They say 53, which is rounded down. It's very easy to select Rounding, and then the round down option in the Transform tab for it.

This will tell you the year of birth:

After that, you can clear the other columns as you'd like (or this could mean because you have used transformations in the Transform tab to stop the creation of new columns) The column could be named Age the column.

Things to Know

• Refresh The age calculated in this way will be updated every time you refresh your data. Every time, it'll match the birth date with the date and time when the data refresh was made. That method uses an algorithm used for pre-calculating the age. If, however, you want the calculation of age to be carried out dynamically using DAX Here's a description of the method which you can make use of.
• What's the reason? Power Query Advantages of using age calculation in The benefit of using age calculation with Query is that the calculation happens during the refresh of your report. It is done using an application that makes the calculation much easier and there is no extra overhead to do it using DAX as a measurement of runtime.
• Additional scenarios This is not an method to calculate the age of a product solely based on birth date. This method can be used to calculate the age of inventory in products and the variations between two dates or times from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc of Computer engineering. He has more than 20 years' worth of experience in the area of data analysis, programming, BI, databases and development primarily using Microsoft technologies. He was an official Microsoft Data Platform MVP for nine years (from 2011 until today) because of his dedication toward Microsoft BI. Reza is an incredibly prolific writer and is co-founder with RADACAD. Reza is also co-organizer and co-founder of the 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 several book related to MS SQL BI and also is writing other books. He also was a frequent participant on technical forums online such as MSDN as well as Experts-Exchange and was moderator on the MSDN SQL Server Forums, and holds the MCP and MCSE , as and the MCITP for Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. Additionally, he is the writer of the highly sought-after work Power BI from Rookie to Rock Star, which is completely free and comprises nearly 17000 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 in addition to SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help users find the most effective data solution. He is an avid Data enthusiast.This article was published with the tags 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. You can follow any responses to this entry through the RSS feed.

Post navigation

Use different visual pages to share using various security groups within Power BIAge's Year Calculation that is used for Leap Year in Power BI by using Power Query