Random Number Generator

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. However, as DAX is the most used language usedin several calculation optionsin Power BI, a lot of people don't know about this feature of Power Query. In this blog post I will explain how easy it is to calculateAge in Power BI by using Power BI. The methodis very beneficial in situations where calculation of an agecan be completed in a row by row basis.

Calculate Age from a date

It's the DimCustomer table in the AdventureWorksDW table, which includes the birthdate column. I've removed some columns that aren't required to make it easier to read;

For you to calculate how old every buyer, you will need:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window, pick the Birthdate column first.
• Click on the Add Column Tab. Under the "From Date & Time" section, and then under Date Select the appropriate age range.

That's all there is to it. This calculates the calculate an amount that is the sum of both the Birthdate column as well as the current date and hour.

However, the date that is displayed in the Age column, doesn't seem to be an actual age. It is due to an actual length.

Duration

Duration is a distinct form of data used in Power Query which represents the difference in the two DateTime values. Duration is the combination of four different values:

days.hours.minutes.seconds

This is how you look at the above information. For an individual's perspective you don't want them to look up facts like this. There are methods that can make each of the portions that are equivalent to the duration. When you select the Duration menu , you'll be able to see that you'll be able to get the number of seconds, minutes of hours, days, or years from it.

For aid to assist calculating the age in years such as, say, it is simple to choose Total Years.

Note that the length of this program calculated by days and then subdivided into 365 to provide you with the annual value.

Rounding

Finally, no one says they're 53.813698630136983! They call it 53, then they round it down. It's easy to select rounding as well as Round Down under the Transform tab.

This will give you the number in years:

It's then possible to remove other columns if you'd like (or perhaps you've used the power of transformations in the Transform tab to stop the development of new columns) The column could be changed to an age column or Age:

Things to Know

• Refresh The age calculated using this method is updated every time you refresh your database. And each time it will check the birthdate to the date and the date when the update was completed. This method is an initial calculation of the age. If you want your calculation carried out dynamically with DAX, I've explained a way that you could use.
• The reason for Power Query: Benefits of doing age calculations using Power Query is that the calculation is performed when you refresh your report. You use a tool that makes the calculation easier, and there's no additional cost of doing it with DAX to determine the time of runtime.
• Another option is to use these numbers in use to calculate age, only beginning with the date of birth. This can be used to calculate the inventory age for products , and also for the difference between two dates or dates one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc from Computer engineering. He holds the more than 20 years of expertise in the field of data analysis and BI, database development and programming with a focus using Microsoft technologies. He was an Microsoft Data Platform MVP for 9 consecutive years (from 2011 to the present) for his commitment toward Microsoft BI. Reza has been an active author and co-founder of RADACAD. Reza is also co-founder and co-organizer of the Difinity Conference which is held 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 a few ebooks about MS SQL BI and also is working on other books. In addition, he was a regular participant on technical forums online like MSDN or Experts-Exchange and was the moderator of MSDN SQL Server forums, and is an MCP, MCSE, and the MCITP for Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. He is also the co-author of the famous book Power BI from Rookie to Rock Star, which is free and contains more than 170 pages of content as well as it is a integral part of 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 and SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's dream is to assist users find the right solutions for their data, and he's a Data enthusiast.This entry was posted in Power BI, Power BI from Rookie to Rockstar, Power Query and is covered under Power BI, Power BI from Rookie to Rock Star, Power Query. You can follow any comments to this entry through the RSS feed.

Post navigation

Share various visual pages by various security groups inside Power BIAge Calculation in Years which is able as a way to calculate Leap Year in Power Business Intelligence by using Power Query