Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way that is a simple method of calculating the age. However, as DAX is the most widely used language usedin several calculationsin Power BI, many are unaware of the functions available in Power Query. In this blog article , I'll describe how simple for you to calculateAge for PowerBI using PowerBI. It is a methodis very useful when you need to do the age calculation. Itcan be calculated using an earlier calculated row by row basis.

Calculate Age from a date

That's what's in the DimCustomer table . This is part of the AdventureWorksDW table that acts as the column for birth dates. I've removed the columns that don't need to be there for clarity;

To calculate how old you are for each customer, you will need:

• In Power BI Desktop, Click on Transform Data
• In the PowerQuery Editor window; make sure that you select the first column, namely the Birthdate.
• Go to the add Column Tab and click on the Add Column Tab, which is in the "From Date & Time" section. In the Date, choose Age

That's all there is to it. This will calculate any change to both the Birthdate column, as well as the current date and date.

However, the age that you see when looking into the Age column, doesn't look like an actual age. It's due to the fact that it's a Duration.

Duration

Duration is a unique type of data that is utilized within Power Query which represents the difference in the two DateTime values. Duration is a mixture of four values:

days.hours.minutes.seconds

That's what you'll find in the above information. However, from a users' viewpoint, they shouldn't be required to study information in the same way as those mentioned above. There are ways you can locate every component of time. Through the Duration menu it will show you how you can pull the total amount of seconds, minutes, hours weeks and years from it.

For aid in calculating the age in years such as it is possible to get to the Total Year:

The duration is determined in days. It is then divided into 365 days for the year to get the total.

Rounding

Truthfully, no one identifies how old their baby is. 53.813698630136983! They are saying 53, which is rounding down. It's simple to select the Rounding and then round down by selecting the Transform tab for it.

This will provide you with the number of years:

You can then cleanse other columns if you'd like (or there is a chance that you've used transformations in the Transform tab to avoid creating new columns) This column is then referred to as column: Age.

Things to Know

• Refresh The age calculated in this manner will be changed at the time of refreshing your database. Every refresh, the data will be matched to the birthdate with the date and day that the refresh of data took place. This method is a way to calculate earlier of age. If, however, you want the age calculation to be dynamically performed with DAX This is what I've described the best way to use DAX.
• The motive behind Power Query: Benefits of calculating an age with Power Query is that the calculation takes place after you refresh the report. It's done with an algorithm that makes the calculation straightforward, and there will not be any additional overhead when calculating it using DAX for a measurement of of runtime.
• Different scenarios This is not meant to be used for calculation of the age of a person based on their birth date. It could be used to determine product inventory and date and time that differ 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 in Computer engineering. He has more than 20 years of experience in data analysis and the BI and databases fields, as well as programming and development mostly using Microsoft technologies. He has been an Microsoft Data Platform MVP for nine consecutive years (from 2011 until the present) due to his dedication to Microsoft BI. Reza is a regular blog writerand founding editor and founder of RADACAD. Reza is also co-founder and the co-organizer of Difinity the conference which takes place 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 a number of books about MS SQL BI and also is working on other books. He was also a frequent participant in online forums on technical issues like MSDN and Experts-Exchange and was the moderator for the MSDN SQL Server forums, as well as an MCP and MCSE as well as an MCITP in Business Intelligence. He was the creator of the New Zealand Business Intelligence users group. In addition, he's the author of the very popular the book Power BI from Rookie to Rock Star, which is available for free and has more than 1800 pages of material and an edition of the Power BI Pro Architecture published by Apress.
The speaker is an International speaker for Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL users ' groups. And He is a Microsoft Certified Trainer.
Reza's desire is to assist you find the ideal solutions for data. He's a Data enthusiast.This article was uploaded within Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great guide to bookmark.

Post navigation

Share various visual pages using different Security Groups in Power B.I.A.'s Calculation of Years that is used to calculate Leap Year in Power BI by using Power Query