Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, since DAX is by far the most used language usedin several functionsin Power BI, the majority of users don't recognize the feature available in Power Query. In this article I will discuss how simple it is to calculateAge in Power BI in conjunction with PowerBI. It is a great methodis very beneficial in cases where the calculation of the agecan be completed on a pre-calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table that makes up the AdventureWorksDW table, which is an age column. I've removed some additional columns in order to make the table easier to understand.

In order to calculate what age each consumer, all you have to do is:

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

That's all there is to it. It can calculate an amount that's the product of the column for birthdate, Birthdate column as well as the current date as well . the time.

The age, however, when it appears within this Age column, does not really look like an age. This is because it's a duration.

Duration

Duration is a special kind of data structure that can be found inside Power Query which represents the difference between two DateTime values. Duration is a mix from four different numbers.

days.hours.minutes.seconds

You will see in the above values. In reality, from an individual's view, they shouldn't need to search for specifics like those mentioned above. There are methods that could be used to determine every minute of the time. using the Duration menu option you'll find the ratio of seconds to minutes, hours, days and years out of it.

For calculating the age in years such as, for instance you just need to go the Total Years.

The duration is calculated in days , then divided by 365 to calculate an annual number.

Rounding

Also, nobody claims to be 53.813698630136983! They state it as 53, and then round it down. It is easy to select the Rounding option , then round down the Transform tab.

This will reveal your age in years:

It is also possible to remove other columns should you wish (or you could have utilized transformations within the Transform tab to avoid creating new columns.) You could name this column as Age.

Things to Know

• Refresh The estimated age using the method gets updated each time you refresh your dataset. Every every time, it'll be comparing your birthdate to the date and date at the time of refresh. This method is a pre-calculation of an age. If, however, you require that the calculation be dynamically performed, by using DAX, here's how I described a way that you can make use of.
• The reasons to use Power Query: Benefits of performing age calculations in Power Query is that the calculation is carried out when you refresh your report. This is performed by using a tool which makes calculation easier, and you don't have to bear an additional cost to calculate the calculation using DAX as a measurement of the runtime.
• Another scenario is that this isn't employed to calculate the age by birthdate. This can be used to calculate the inventory of items as well as the difference between two dates and dates from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc from Computer engineering. The engineer has over 20 years' expertise in the field of data analysis, database programming, BI, development and programming primarily focused upon Microsoft technologies. He is an official Microsoft Data Platform MVP for nine consecutive years (from 2011 until the present) because of his devotion toward Microsoft BI. Reza has a successful writer and co-founder of RADACAD. Reza is also the co-founder and co-organizer of the 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 a few books on MS SQL BI and also is working on other books. He also was a frequent member of online forums for technical issues such as MSDN and Experts-Exchange as well as moderator of MSDN SQL Server forums, and is an MCP and MCSE as well as an the MCITP for BI. As the group's director, he is responsible for 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 accessible for free and contains more than 17000 pages of data and another book titled 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, along with SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help you discover the most effective solutions to deal with data. And Reza is a Data enthusiast.This story was featured 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. The following article is a good resource to save to your bookmarks.

Post navigation

- Share different visual pages by using Different Security Groups that PowerBIAge uses for Year Calculation that works for Leap Year in Power BI by using Power Query