Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. However, since DAX is the main language usedin numerous calculationsin Power BI, many are unaware of this feature of Power Query. In this article, I'll demonstrate the process of how to calculateAge within Power BI using Power BI. The methodis extremely useful when the estimation of the agecan be calculated using an earlier calculated row by row basis.

Calculate Age from a date

Below you can see the DimCustomer table, which is an integral part of the AdventureWorksDW table. It acts as"the birth date" column. I've taken out a few of the extra columns for easier study.

In order to calculate your age for each of your customers All you have to do is:

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

That's it. This is the method you calculate an amount that is the total of the Birthdate column, along with the date and time of the present.

But, the age that appears under"age" in the Age column, but doesn't appear to be the actual age. It's because it's actually a time period.

Duration

Duration is a unique kind of data within Power Query which represents the difference between two DateTime values. Duration is a combination of four numbers:

days.hours.minutes.seconds

and that is how you interpret the data above. For the view of the user, it's not necessary for them to look up the details of this. There are techniques that can be able to get every component of the time. If you choose from the menu of Duration, you will find that it is possible to take the number of seconds and minutes or hours, days, and years from it.

To aid in calculating the age in years for example you can hit Total Year:

It is important to note that the duration is measured in days . Then, it is divided by the number of days to calculate the annual amount.

Rounding

No one declares they are 53.813698630136983! They are 53, which is rounded down. It is easy to select Rounding and Round Down from the Transform tab.

This will give you an indication of your age in years:

Clean up the other columns if you'd like (or perhaps you've made use of transformations through the Transform tab to avoid having the task of creating new columns) This column can be renamed as follows: Age: column Age:

Things to Know

• Refresh The age that is calculated in this manner will be updated each time you are refreshing your information. Each time, it compares the birth date to the date and moment of the refresh. This method is the pre-calculation of age. If, however, you require the calculation be made dynamically using DAX this is the way I explained the method you can apply.
• The motive for Power Query: Benefits of doing an age calculation using Power Query is that the calculation is carried out while you refresh your report, and using an instrument which makes the calculation simpler and faster, plus there's no additional cost when calculating it using DAX to measure the time.
• Alternative scenarios It can't be used to calculate the date of birth. This can be used to calculate the inventory-level age of items as well as the differences between two dates and times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc on Computer engineering. He has more than twenty years' experience in the area of data analysis, database programming, BI and development primarily in Microsoft technologies. He was an official Microsoft Data Platform MVP for nine consecutive years (from 2011 until the present) because of his enthusiasm of Microsoft BI. Reza is a prolific writer for blogs as well as the co-founder and director of RADACAD. Reza is also co-founder and co-organizer for Difinity Conference. Difinity Conference located 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 also wrote a few books about MS SQL BI and also is working on more books. He was also a frequent participant in online forums dealing with technical matters like MSDN and Experts-Exchange and was also moderator of the MSDN SQL Server forums, and is an MCP and MCSE as well as an MCITP of Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. He is also the co-author of the well-known book Power BI from Rookie to Rock Star, which is available for free and includes more than 170 pages of content. Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
The speaker 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 passion is to help users to find the ideal data solution. He is an avid Data enthusiast.This post was published by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed under Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. Please leave a comment.

Post navigation

- Share Multiple Visual Pages with Different Security Groups in Power BIAge in Years Calculation which works for Leap Year in Power BI using Power Query