Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that is a quick and easy method of calculati ng the age. However, as DAX is the most widely used language usedin several calculati onsin Power BI, a lot of people don't have any idea about the feature available in Power Query. In this blog post, I'll discuss how easy it can be to calculateAge in Power BI using PowerBI. It is a methodis very beneficial when you need to calculate the calculati ons for age.can be calculated on an earlier calculated row-by-row basis.

Calculate Age from a date

The table is called one of DimCustomer table , and it comes part of the AdventureWorksDW table that functions as the "birthdate" column. I've removed columns that aren't required to make it more readable;

For you to calculate your age every client, you'll need:

• In Power BI Desktop, Click on Transform Data
• In Power Query Editor window, be sure to choose the first column, namely the Birthdate.
• Go to the add Column Tab and click on the Add Column Tab, which is within the "From Date & Time" section. Under Date, select Age

That's all there is to it. This will calculate any variations between the Birthdate column, and the current time and date.

The age you can see when looking in the Age column, doesn't look like an actual age. It's because it's a real duration.

Duration

Duration is a specific type of data that is utilized to calculate the duration of a query in Power Query which represents the different between two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

And that's exactly what you'll discover in the details above. But from a user's perspective they shouldn't be expected to go and read the details like the ones above. There are ways you can get each of the components of the time. When you use the Duration menu you'll see that you can extract the amount of seconds, minutes, hours days , and years from it.

In order to assist in calculating the age in years such as it is possible to hit the Total Year:

The duration is determined in days. It is later divided in 365 hours to yield the yearly amount.

Rounding

It's the truth, no one has stated the age of their child as 53.813698630136983! They state 53, which is the number rounded down. It is easy to select Rounding option and then round down using the Transform tab.

This will provide you with the number in years:

It's then possible to remove other columns if you'd like (or there's a possibility that you've applied transforms 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 this way is altered at the time of refreshing your data. Every every time it is refreshed, it'll match the birthdate with the date of the day and the time the data refresh took place. This method is a way to calculate earlier of age. If, however, you would like the calculation of age to be dynamically performed using DAX This is what I've explained how to utilize.
• The rationale behind Power Query: Benefits of calculating an age by using Power Query is that the calculation is performed after you refresh the report. This is accomplished using an algorithm that makes the calculation more simpleand less complicated, so there won't be additional work involved in doing it using DAX in order to determine runtime.
• Different scenarios This is not intended for the calculation of the age of a person based on their birth date. It could be used to determine inventory age of the product and differing dates and dates 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 expertise in data analysis, the BI and databases fields, as well as programming and development primarily employing Microsoft technologies. He has been a Microsoft Data Platform MVP for nine consecutive years (from 2011 until now) for his commitment to Microsoft BI. Reza is a regular blog writer, and also the co-founder and editor of RADACAD. Reza is also the co- founder, as well as coordinator of Difinity the conference held located within 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 books on MS SQL BI and also is writing other books. He was also a frequent member of online forums on technical issues such as MSDN and Experts-Exchange as well as moderator of MSDN SQL Server forums, and is an MCP and Microsoft Certified Specialist (MCSE) as well an MCITP in BI. He is the founder of the New Zealand Business Intelligence users group. Additionally, he's the author of very popular publication Power BI from Rookie to Rock Star, which is available for download for free and includes more than 17000 pages of information and the Power BI Pro Architecture published by Apress.
Speakers are 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 ideal solutions for data, and he's a Data enthusiast.This entry was posted on the topic of 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

Share different visual pages with different security groups in Power to use BIAge's Calculation of Years that is used to calculate Leap Year in Power BI by making use of Power Query