Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. But, because DAX is the most commonly used language usedin several calculationin Power BI, a lot of people don't have any idea about this feature of Power Query. In this blog article, I will demonstrate how easy to calculateAge in Power BI with Power BI. The methodis extremely helpful for situations in which the calculation of the agecan be performed in a row-by-row basis.

Calculate Age from a date

It's the DimCustomer table in the AdventureWorksDW table which as an age column. I've removed some columns that don't need to be there to make it more readable;

For you to calculate an age for each buyer, all you need to do is:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window, start by selecting the Birthdate column.
• Click on the Add Column Tab. Under the "From Date & Time" section, and then under Date choose the date range.

This is all there is to it. this calculates the calculate an amount that is the total in the Birthdate column in addition to the current date and the time.

However, the age that appears in the Age column, doesn't really appear like an age. This is because it's an actual length.

Duration

Duration is a distinct form of data utilized in Power Query which represents the distinction between two DateTime values. Duration is a combination of four numbers:

days.hours.minutes.seconds

This is how you view these values. From one's own perspective, you don't want them to look up facts like this. There are ways to get each portion that is an amount of time. When you go to the Duration menu , you'll find that you'll be able extract the number of seconds, minutes, hours, days and years out of it.

In order to aid to assist calculating the age in years such as, for instance it is simple to choose Total Years.

Make note of the fact that the duration of your program will be measured in days . Then, it is divided into 365 to provide you with the annual cost.

Rounding

There is no way to prove they're 53.813698630136983! They use the term 53, and then then round it down. It's simple to select Rounding or Round Down under the Transform tab.

This will give you the number in years:

It's then possible to clean other columns if you'd like (or perhaps you've taken advantage of transformations using the Transform tab to stop the making of columns) This column may be changed to an age: column and Age:

Things to Know

• Refresh The age that is calculated using this method is updated every time you are refreshing your database. Each time, it will compare the birthdate with the date and date at which the refreshed. This method is pre-calculating the age. If you want your calculation carried out dynamically using DAX here I provided a method you could apply.
• The rationale behind Power Query: Benefits from performing calculations for age in Power Query is that the calculation is performed in the course of refreshing your report. This is done by using an application that makes the calculation more efficient, and there's no additional expense of using DAX to gauge runtime.
• Another possibility is that they aren't intended to be used to calculate age. They only begin from the birth date. This can be used to calculate the date of inventory for products and also the different among two dates 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 degree in Computer engineering. He holds over 20 years of expertise in the field of data analysis data databases, BI and programming with a focus using Microsoft technologies. He was a Microsoft Data Platform MVP for nine years in a row (from 2011 until now) due to his dedication to Microsoft BI. Reza remains an active author and co-founder of RADACAD. Reza is also co-founder as well as coordinator of Difinity Conference which 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 also written a few books in the field of MS SQL BI and also is working on more books. The author was a frequent participant on technical forums online such as MicrosoftDN as well as Experts-Exchange and was the moderator of MSDN SQL Server forums, and holds the MCP as well as MCSE and MCITP for Business Intelligence. He is the creator of the New Zealand Business Intelligence users group. Additionally, he's the creator of the popular guidebook Power BI from Rookie to Rock Star, which is free and contains more than 170 pages of material as well as it is element of Power BI Pro Architecture published by Apress.
It is an International Speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's dream is to assist users find the most effective information solution. He is Data enthusiast.This post was uploaded in Power BI, Power BI from Rookie to Rockstar, Power Query and is covered with Power BI, Power BI from Rookie to Rock Star, Power Query. Bookmark the permalink.

Post navigation

Share different visual pages with different security groups in Power BIAge Calculation in Years which can be used for calculate Leap Year in Power BI with the help of Power Query