Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. However, since DAX is the main language usedin numerous calculationsin Power BI, many are not aware of the functions available in Power Query. In this blog , I'll discuss how easy to calculateAge in Power BI using Power BI. This methodis extremely useful in situations where your Age calculationcan be carried out in a pre-calculated row rows.

Calculate Age from a date

Below is the DimCustomer table from the AdventureWorksDW table that functions as the "birthdate" column. I've removed the columns that don't have any value for the sake of making it easier to comprehend.

To calculate your average age of each consumer All you need to do is to:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; start by selecting the column titled Birthdate.
• Click on the Add Column Tab, and follow"Add Column" and then on "From Date & Time" section, and under Date, choose Age

That's all there is. this does calculate any variation from the Birthdate column along with the current date and time.

However, the age which shows up in the Age column does not actually look like an age. That is because it is a Duration.

Duration

Duration is a specific kind of data types used as a the data type used in Power Query that represents the differences between the DateTime and DateTime values. Duration is made up that includes four values:

days.hours.minutes.seconds

This is the method to find the above values. However, from a user's view, they shouldn't have to research information like that. There are methods by which you can obtain every single segment that's durational. If you go to the Duration menu, you'll notice that you can find the duration of seconds to minutes, hours days, and years from it.

For calculating the age in years such as this, it is easy to get Total Year:

The duration is measured in days , and after that is divided by the amount of days in order to calculate the annual number.

Rounding

Truth is, nobody declares their age is 53.813698630136983! They use the term 53 and then they round it down. You can choose Rounding after which you can select Round Down on the Transform tab.

This will show you your age in years:

Make sure that the other columns are cleaned If desired (or you could find that you made use of transformations by using the Transform tab to avoid making new columns) This column should be named column Age.

Things to Know

• Refresh The date calculated in this way will get refreshed every time you're refreshing your data. And each time, it will compare dates of birth and the dates and times for refreshing. This method is the initial calculation of age. If you need the calculation of age performed dynamically using DAX This is the method I've described how to use.
• The reason for Power Query: Benefits of performing age calculations in Power Query is that the calculation is performed at the moment when you refresh your report. The report is refreshed with the tool that makes calculation more simple, and there's no cost associated with formulating it using DAX for a measure time.
• Another reason is that it's not intended to calculate age solely based on birthdate. This is a way to calculate the inventories of products as well as the differences between two 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 earned an BSc of Computer engineering. He holds greater than twenty years' expertise in data analysis and BI, database programming and development primarily based on Microsoft technologies. He was a Microsoft Data Platform MVP for 9 years (from 2011 until the present) due to his commitment in Microsoft BI. Reza is a frequent blog writerand is the editor and co-founder of RADACAD. Reza is also the co-founder and coordinator of Difinity event located 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 books on MS SQL BI and also is working on additional books. He also was a regular member of online forums on technical questions like MSDN and Experts-Exchange and was a moderator of MSDN SQL Server forums, and is an MCP as well as the MCSE, as well as an MSCITP of the BI. He is the head of the New Zealand Business Intelligence users group. He is also the creator of the book , which is extremely well-loved Power BI from Rookie to Rock Star, which is free and contains greater than 1700 page of content as well as a book titled Power BI Pro Architecture published by Apress.
His qualifications are that he is 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 helping users find the best data solution. He is a Data enthusiast.This article was posted in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed within Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. Please leave a comment.

Post navigation

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