Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, because DAX can be the largest and most widely used language usedin several functionsin Power BI, many people do not know the functions available in Power Query. In this article, I'll discuss how simple it is to calculateAge in Power BI using Power. This methodis extremely useful for situations when the calculation of the agecan be completed on a pre-calculated row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table that is included in the AdventureWorksDW table and is an old column. I've removed some of the additional columns, to make it easier to understand.

To calculate how old each customer, the only thing you need is to:

• In Power BI Desktop, Click on Transform Data
• Inside the Power Query Editor window; start by selecting the Birthdate column.
• Click on the Add Column Tab that is located in the "From Date & Time" section, and under Date Select the age range.

That's about it. It can calculate an amount that is the sum of the column for birthdate, Birthdate column and the current date and time.

The age, however, when it appears in that Age column, does not really appear like an age. It is due to the fact that it is not a length.

Duration

Duration is a distinct type of data format used inside Power Query which represents the distinction between two DateTime values. Duration is a combination with four values:

days.hours.minutes.seconds

That's what you'll notice in the values listed above. However, from an individual's view, they shouldn't need to research specifics that are similar to the ones mentioned above. There are methods to get every part of the time. By using the Duration menu, you'll see the number of seconds to minutes, hours, days and years.

To calculating the age in years such as, for instance it's as easy as going to Total Years.

The duration is calculated by days and then divided by 365, giving you the yearly value.

Rounding

Also, nobody claims they are 53.813698630136983! they say it as 53 with a rounding down. You can select the Rounding option . You can then round down the Transform tab.

This will give you an indication of that you're old enough to be

It is also possible to tidy other columns, if desired (or you could have made use of transformations in the Transform tab to avoid creating new columns) You can name this column: Age.

Things to Know

• Refresh The age calculated using this method shall be updated each time you refresh your data. And each every time, it'll be comparing your birthdate with the date and moment of the refresh. This method is the pre-calculation of your age. If you want the age calculation to be dynamically performed, employing DAX, here's how I explained a method you could use.
• The reasons to use Power Query: Benefits of doing age calculations using Power Query is that the calculation takes place when you refresh your report. This is performed by using a tool that makes calculations much easier and there's no need for the extra cost of calculating it with DAX as a measurement of time.
• Another scenario This is not used to calculate the age by birthdate. This can be used to calculate inventory of goods as or the difference in two dates and times from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc from Computer engineering. More than twenty years' experience in data analysis , database programming, BI, development and programming specifically focused specifically on Microsoft technologies. He is an official Microsoft Data Platform MVP for nine consecutive years (from 2011 to now) due to his dedication in Microsoft BI. Reza has a long-running career as a writer and co-founder of RADACAD. Reza is also the co-founder and coordinator of Difinity Conference at New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote several books on MS SQL BI and also is writing some others, He also was a frequent participant in online forums on technical questions like MSDN and Experts-Exchange . He was also a regular participant as moderator for MSDN SQL Server forums, as well as an MCP and an MCSE and the MCITP for BI. He is the director of the New Zealand Business Intelligence users group. Also, he's the author of the highly acclaimed publication Power BI from Rookie to Rock Star, which is freely available and contains more than 17000 pages worth of information. There's also another book titled Power BI Pro Architecture published by Apress.
It is an International Speaker in 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 discover the most effective ways to use data, and is an avid Data enthusiast.This article was published in Power BI, Power BI from Rookie to Rockstar, Power Query and related to Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic source to bookmark.

Post navigation

- Share Different Visual Pages by using Different Security Groups in Power BIAge's Year Calculation that works for Leap Year in Power BI with Power Query