Age Calculation

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 calculationin Power BI, many do not know about this feature of Power Query. In this post, I will show you how simple to calculateAge in Power BI with Power BI. This methodis very beneficial for situations in which the calculation of an agecan be conducted in a row-by-row basis.

Calculate Age from a date

This is the DimCustomer table from the AdventureWorksDW table that has a birthdate column. I've removed columns that don't need to be there to make it easier to read;

For you to calculate an age for each buyer, you must have:

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

This is all there is to it. This will calculate an amount that is the sum in the Birthdate column, in addition to the current date and hour.

However, the age 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 different type of data that is utilized for Power Query which represents the variation between two DateTime values. Duration is a combination of four numbers:

days.hours.minutes.seconds

This is how you see the above data. However, for one's own perspective, you don't wish them to research information such as this. There are methods that can get each portion that is an amount of time. When you select the Duration option, you'll see that you can extract the number of seconds, minutes as well as days, hours and years out of it.

To help in calculating the age in years like, for example, it is easy to select Total Years.

Be aware that the duration of the program is measured in days and afterwards divided into 365 in order to provide you with the value for the year.

Rounding

In the end, nobody says they're 53.813698630136983! They call it 53, and then then round it down. It's simple to select Rounding and Round Down from the Transform tab.

This will give you the number in years:

Then, you can clean other columns if you'd like (or perhaps you've used the power of transformations using the Transform tab to avoid the creation of new columns) This column may be named as an Age: column and Age:

Things to Know

• Refresh The age that is calculated using this method is updated every time you are refreshing your database. Each time, it will match the birthdate to the date and date of the refresh. This method is a pre-calculation of the age. If you're in need of your calculation run dynamically by DAX, I have presented a method you can apply.
• The reason behind Power Query: Benefits from performing calculations for age in Power Query is that the calculation takes place when you refresh your report. This is done by using an instrument that makes the calculation easier, and there's no cost of doing it with DAX to measure the time of runtime.
• Another possibility is that they aren't for calculating age only starting with the date of birth. It could be used to calculate the inventory age for products , and also for the difference among two 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 of Computer engineering. He has an extensive 20-year' expertise in the field of data analysis as well as BI, databases development, and programming mostly on Microsoft technologies. He has been a Microsoft Data Platform MVP for nine consecutive years (from 2011 to the present) due to his dedication in the field of Microsoft BI. Reza can be found as an active writer and co-founder at RADACAD. Reza is also co-founder and coordinator of 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 couple of publications about MS SQL BI and also is writing some others, He was also a frequent participant on technical forums online like MicrosoftDN as well as Experts-Exchange and was also the moderator of MSDN SQL Server forums, and holds the MCP, MCSE, and the MCITP for Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. In addition, he is the co-author of the famous workbook Power BI from Rookie to Rock Star, which is free with more than 170 pages of information as well as it is part of the 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 best information solution. He is Data enthusiast.This post was uploaded within Power BI, Power BI from Rookie to Rockstar, Power Query and is classed in Power BI, Power BI from Rookie to Rock Star, Power Query. The following is apermalink.

Post navigation

Share visual pages on different security groups in Power BIAge Calculation in Years which is able in order to calculate Leap Year in Power Business Intelligence by using Power Query