Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple approach that provides a simple method that is a simple method of calculating the age. However, as DAX is the largest and most used language usedin several calculationsin Power BI, many don't know about the functions available in Power Query. In this blog article I'll show you how easy the process is calculateAge within Power BI using PowerBI. This methodis very beneficial when you have to perform the age calculation.can be done using an earlier calculated row-by-row basis.

Calculate Age from a date

Here is this is the DimCustomer table , which comes taken from the AdventureWorksDW table that acts as the column for birth dates. I've removed columns that weren't needed to make it easier to read;

To calculate your age for every buyer, you need is:

• In Power BI Desktop, Click on Transform Data
• In Power Query Editor window, be sure that you select the column titled Birthdate first.
• Click on the Add Column Tab located beneath the "From Date & Time" section. Then, under Date, select Age

It's that simple. it. It will calculate any variation to that column, the Birthdate column, and the current date and time.

The age you see when looking through the Age column, isn't looking like an actual age. It's because it's an actual Duration.

Duration

Duration is a specific kind of data used in Power Query which represents the different between two DateTime values. Duration is a mixture of four values:

days.hours.minutes.seconds

That's what you'll see in the information above. But from a user's viewpoint, they shouldn't be required to study information like those above. There are many ways to locate every component of time. Through the Duration menu you'll see that you can extract the quantity of seconds, minutes, hours weeks and years from it.

For aid in calculating the age in years for instance , it's easy for you to reach Total Year:

Note that the duration is measured in days and was later divided in 365 hours to produce the annual sum.

Rounding

The truth is that no one states that their kid's age is 53.813698630136983! The number is 53. This is the number rounded down. It's easy to select the Rounding and then round down to the Transform tab.

This will give you the year-old number:

It's also possible to tidy other columns as you'd like (or it is possible that you've used transformations in the Transform tab to avoid the need to create new columns) Then, you can name this column"Age"

Things to Know

• Refresh The age calculated in this way will be modified at the time of refreshing your data. Each refresh, the data will be matched to the birthdate with the date on which the data refresh occurred. This method is an earlier calculation of age. If you'd like the calculation to be carried out dynamically with DAX This is the method I've shown how to use DAX.
• The motivation behind Power Query: Benefits of calculating an age using Power Query is that the calculation is performed whenever you refresh your report. This is accomplished using an algorithm that makes the calculation easy, and there will not be extra overhead in calculating it with DAX for a measurement of runtime.
• Different scenarios This is not intended to be used to calculate age by birthdate. This can be used to calculate product inventory and date and time that differ from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc with a major in Computer engineering. He has more than 20 years of experience in data analysis , database programming, BI and development generally with Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years in a row (from 2011, until now) for his dedication in Microsoft BI. Reza is a regular blog writer, and the founder and the editor for RADACAD. Reza is also the co-founder and co-organizer of Difinity the conference held 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 has written a number of books about MS SQL BI and also is writing other books. He also was a regular participant in online forums on technical issues , such as MSDN and Experts-Exchange and was moderator on the MSDN SQL Server forums, and is an MCP , MSSE as well as an MCITP in Business Intelligence. He was the creator of the New Zealand Business Intelligence users group. In addition, he's an author of the extremely popular book Power BI from Rookie to Rock Star, which is offered for free and features more than 1800 pages of material and it's 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 users groups. And He is a Microsoft Certified Trainer.
Reza's desire is to assist you find the best solutions for data. He's a Data enthusiast.This entry was posted as 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 resource for you to bookmark.

Post navigation

Create Different Visual Pages by using different security groups within Power the BIAge's Calculation of Years that is used to calculate Leap Year in Power BI by making use of Power Query