Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, because DAX can be the largest and most popular language usedin several functionsin Power BI, many users do not realize the function available in Power Query. In this article, I'll explain how easy it is to calculateAge in Power BI using Power. It is a great methodis extremely helpful in cases where the calculation of the agecan be calculated on a row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table, which is an element of the AdventureWorksDW table which includes one of the columns that is a time column. I've removed a few extra columns so that it is easier to understand.

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

• In Power BI Desktop, Click on Transform Data
• The Power Query Editor window; begin by selecting the Birthdate column.
• Click on the Add Column Tab found under the "From Date & Time" section. Under Date Select the age range.

It's that simple. it. This can calculate an amount which is the sum of the column for birthdate, Birthdate column and the actual date and the time.

The age, however, when it appears within this Age column, however, it does not seem to look like an actual age. It's because it's the duration.

Duration

Duration is a special kind of format for data with Power Query which represents the differences in two DateTime values. Duration is made up comprising four figures:

days.hours.minutes.seconds

This is what you will find in the values above. But, from a person's view, they shouldn't need to seek out specifics such as those above. There are methods to get every part of the time. using the Duration menu option you'll observe the quantity of seconds to minutes, hours and years.

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

The duration is measured in days , then divided by 365, giving you an annual number.

Rounding

Finally, no one claims you are 53.813698630136983! They say 53, with a rounding down. You can choose the Rounding option . You can then round down in the Transform tab.

This will reveal the age you've reached in your years.

It's also possible to tidy other columns, at your discretion (or you might have utilized transformations in the Transform tab to avoid creating new columns) This column can be named. column as Age.

Things to Know

• Refresh The age that is calculated using this method shall be updated each time you are refreshing your data. Each time, it will check your birthdate with the date and time at the time of refresh. This method is the pre-calculation of your age. If, however, you need the age calculation to be dynamically performed, employing DAX, here's how I have explained a procedure is possible to use.
• Reasons to choose Power Query: Benefits of performing calculations on age in Power Query is that the calculation takes place when you refresh your report. This is done by making use of a tool to make the calculation much simpler, and there's no need for the added cost of doing it using DAX to measure runtime.
• Another scenario , this cannot be used to calculate the age of a person based on their birthdate. It could be used to determine inventory of products as and the difference between two dates and times 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 of Computer engineering. Over 20 years' expertise in the field of data analysis, database programming, BI, development and programming specifically focused on Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years (from 2011 to present) because of his commitment toward Microsoft BI. Reza has a long-running career as a writer and is co-founder with RADACAD. Reza is also co-founder as well as co-organizer for 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 has written several books on MS SQL BI and also is writing some others, He also was a frequent member of online forums for technical questions like MSDN and Experts-Exchange . He was also a regular participant as moderator for MSDN SQL Server forums, and holds the MCP as well as an MCSE. He is also MCITP for Business Intelligence. He is the director of the New Zealand Business Intelligence users group. In addition, he's the creator of the incredibly popular workbook Power BI from Rookie to Rock Star, which is freely available and contains more that 17000 pages of information and an additional book called Power BI Pro Architecture published by Apress.
This speaker has been 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 main goal is to assist you find the most efficient solutions for data, and is a Data enthusiast.This blog post appeared in Power BI, Power BI from Rookie to Rockstar, Power Query and connected to Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic resource to save to your bookmarks.

Post navigation

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