Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, because DAX can be the most popular language usedin numerous calculationsin Power BI, many don't know about this feature of Power Query. In this blog post , I will discuss how easy for you to calculateAge within Power BI using Power BI. This methodis very helpful when you need the ability to calculate the calculation of age.can be made by calculating the row-by-row basis.

Calculate Age from a date

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

To calculate how old every client is, all you need to do is:

• In Power BI Desktop, Click on Transform Data
• In the PowerQuery Editor window, select the column that is the primary one, birthdate.
• Select the Add Column Tab, then under "From Date & Time" section, and under Date select the date range.

This is all there is to it. This is how you calculate what is the ratio between Birthdate column, as well for the current date as well as time.

The age displayed in the Age column does not appear to be an age. This is because it's not an actual duration.

Duration

Duration is a specific type of data used within Power Query which represents the variations between the two DateTime values. Duration is a mix of four numbers:

days.hours.minutes.seconds

This is the best way to comprehend the above figures. However, from the viewpoint of the user it's not expected of them to comprehend the details of this. There are methods that can determine each part of the time. By choosing the Duration menu you'll be able see the quantity of minutes, seconds, hours, and years.

To aid for calculating the age in years such as, for instance it is easy to select Total Years.

It is essential to know that time is expressed in terms of days and following that, divided by the number of days to give you an annual number.

Rounding

At the final point, nobody claims that they are 53.813698630136983! They state 53, which is reduced to. It's simple to select Rounding and then round down option from the Transform tab.

This will show you the years of age:

After that, you are able to clean the other columns in the event that you'd like (or this could mean that you used transformations on the Transform tab to prevent creating new columns) The column may be renamed to Age column Age.

Things to Know

• Refresh The age calculated in this manner will be updated every time you refresh your data. Every time, it'll match the date of birth to the date and date when the refresh took place. That method involves an algorithm used for pre-calculating age. If you need the calculation of age to be done dynamically using DAX Here's how I explained the method that you could use.
• What is the reason? Power Query Benefits of using age calculation in The benefit of using age calculation with Query is that it is calculated at the time of refresh of your report. It's accomplished using an application that makes the calculation much easier as well as there is no extra expense in calculating it using DAX as a measurement of time.
• Other scenarios There isn't an method to calculate age only by birthdate. This is a method to calculate the age of inventory for products as well as the difference between 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 is more than twenty years old and has worked in the field of data analysis and programming, BI, databases and development, mainly using Microsoft technologies. He has been a certified Microsoft Data Platform MVP for nine consecutive years (from 2011, until today) due to his dedication in Microsoft BI. Reza is a prolific author and is co-founder of 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 some titles in the field of MS SQL BI and also is writing other books. He was also a regular user of forums for technical issues like MSDN and Experts-Exchange and was the moderator for the MSDN SQL Server forums as well as holding the MCP and the MCSE as in addition to the MCITP for Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. In addition, he is the writer of the highly sought-after book Power BI from Rookie to Rock Star, which is free and contains nearly 17000 pages of information as well as Power BI Pro Architecture, which is the 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 as well as SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help you find the most efficient data solution. He is a Data enthusiast.This post was filed by Reza on Power BI, Power BI from Rookie to Rockstar, Power Query and was tagged with Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. The following entry was filed under Power BI.

Post navigation

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