Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. But, because DAX is the most popular language usedin several calculationsin the Power BI platform, many don't know about the function that is available in Power Query. In this blog post , I will describe how simple to calculateAge in Power BI using Power BI. This methodis extremely helpful in cases where the calculation of agecan be carried out using a previously calculated row by row basis.

Calculate Age from a date

That's the DimCustomer table from the AdventureWorksDW table which as an age column. I've removed columns that aren't necessary in order to make it easier to read;

To calculate the exact age each buyer, you will need:

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

That's all there is to it. This will calculate an amount that is the total of the Birthdate column as well as the current date and the time.

However, the age which is displayed in the Age column, doesn't really appear like an age. This is because it's an actual length.

Duration

Duration is a distinct form of data that is utilized for Power Query which represents the differences between the two DateTime values. Duration is the result of four different values:

days.hours.minutes.seconds

This is how you look at these values. However, for one's own perspective, you don't want them to find facts like this. There are ways to determine each component that is an amount of time. If you select the Duration menu you'll notice that you can extract the amount of seconds or minutes, hours, days and years from it.

In order to aid when calculating the age in years such as, for instance, it is simple to choose Total Years.

Make note of the fact that the duration of the program is measured by days and then subdivided into 365 in order to provide you with an annual amount.

Rounding

In the end, nobody says they are 53.813698630136983! They use the term 53, then they round it down. It's easy to select the Rounding option and Round Down by clicking on the Transform tab.

This will show you your age in years:

It's then possible to tidy other columns as you'd like (or perhaps you've taken advantage of transformations using the Transform tab to stop the creation of new columns) The column could be renamed as the Age column or Age: column.

Things to Know

• Refresh The age calculated by this method will be updated every time you are refreshing your database. and each time will compare the birthdate with the date and the date at which the refreshed. This method is an earlier calculation of the age. If you're looking for calculations to be dynamically done using DAX here I explained a way that you could use.
• The rationale for Power Query: Benefits of doing age calculations using Power Query is that the calculation is completed during the refresh of your report. It's done using an instrument that makes the calculation more efficient, and there's no added expense to using DAX to measure runtime.
• Another possibility is that they aren't in use to calculate age, only beginning with the date of birth. This can be used to calculate the time of inventory on items, as well as the distinction of two date or times 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 with a degree in Computer engineering. He has over 20 years of experience in the fields of data analysis and BI, database development and programming, mostly based on Microsoft technologies. He has been an Microsoft Data Platform MVP for nine years in a row (from 2011 to the present) due to his dedication in the field of Microsoft BI. Reza is an avid author and co-founder of RADACAD. Reza is also co-founder and co-organizer for 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 several publications regarding MS SQL BI and also is working on more books. The author was a frequent member on online technical forums such as MSDN or Experts-Exchange and was the moderator of MSDN SQL Server forums, and is an MCP as well as MCSE and MCITP for BI. He is the creator of the New Zealand Business Intelligence users group. Also, the writer of the famous workbook Power BI from Rookie to Rock Star, which is completely free and includes more than 170 pages of content and is component 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 passion is to help users find the best information solution. He is Data enthusiast.This entry was posted in Power BI, Power BI from Rookie to Rockstar, Power Query and is listed within Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

Create different visual pages and share them with various security groups inside Power BIAge Calculation in Years that can be used by Power BI to calculate Leap Year in Power BI with the help of Power Query