Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, as DAX can be the most commonly used language usedin numerous computationsin Power BI, many are unaware of this feature available in Power Query. In this blog post , I will discuss how easy it is to calculateAge within Power BI in Power BI. This methodis exceptionally helpful in situations where you need in order to calculate the the calculations of age.can be done using a calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table that is an element of the AdventureWorksDW table which includes the birthdate column. I've removed a few columns that aren't required in order to make it more user-friendly;

In order to calculate the age of each client has reached, the only thing you must do is to:

• In Power BI Desktop, Click on Transform Data
• In the Window of the Power Query Editor; select the first column, Birthdate.
• You can go to the Add Column Tab, then under "From Date & Time" section. Then, under Date Select the age range.

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

However, the date that appears in the Age column, but it doesn't appear to be an actual age. It's because it's an actual length.

Duration

Duration is a special type of data that is used in Power Query which represents the variations in Two DateTime values. Duration is composed with four values:

days.hours.minutes.seconds

This is the best way to comprehend the above figures. However, from the viewpoint of the user, it's not the norm for them to know the exact values of this. There are ways to find each segment of time. Selecting the Duration menu you'll find the number of minutes, seconds, hours, months and days from it.

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

It is vital to keep in mind this: the measurement is in day and afterwards divided by the number of days to provide you with the value for the year.

Rounding

As a final note, no one declares their age is 53.813698630136983! They state 53, which is the number rounded down. It is easy to select Rounding then then the Round Down option on the Transform tab for it.

This will give you the age of the years:

Then, you can clean the other columns If you'd like (or you could find that you have used transformations in the Transform tab to prevent creating new columns) This column can be named something like Age the column.

Things to Know

• Refresh The age calculated using this method will be updated every time you refresh your data. Each time, it's going to match the date of birth to the date and date that the refresh took place. This method uses an algorithm used for pre-calculating the age. If you want the calculation of age to be dynamically performed using DAX This article will explain the method to make use of.
• What is the reason? Power Query benefits of age calculation with Power Query is that it is calculated when you refresh your report. It's done with an application that facilitates the calculation as well as there is no extra cost to calculate it using DAX for a measure of the runtime.
• Other scenarios It isn't a method to calculate age just by birthdate. This method can be used to calculate the age of inventory of products and also to determine the differences between two dates and times from one.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc of Computer engineering. He has over twenty years of experience in the area of data analysis, database programming, BI and development, mainly using Microsoft technologies. Reza has been an official Microsoft Data Platform MVP for nine consecutive years (from 2011, until today) because of his dedication toward Microsoft BI. Reza can be described as an prolific writer and is co-founder of RADACAD. Reza is also the 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 a few titles regarding MS SQL BI and also is writing other books. He also was a frequent participant in technical forums online such as MSDN as well as Experts-Exchange and was moderator for the MSDN SQL Server forum and holds the MCP and MCSE as well as the MCITP for Business Intelligence. He is also the head of the New Zealand Business Intelligence users group. In addition, he is the author of the highly sought-after book Power BI from Rookie to Rock Star, which is free and includes over 17000 pages of content and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
Speakers are 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 purpose is to help you to find the most effective data solution. He is an avid Data enthusiast.This post was published under 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 filed under Power BI. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

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