Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. However, as DAX is the most widely used language usedin numerous calculationsin Power BI, a lot of people do not know about the feature available in Power Query. In this blog , I'll explain how easy it is to calculateAge in Power BI and Power BI. This methodis highly beneficial when your age calculationcan be done on a pre-calculated row to row basis.

Calculate Age from a date

Below is the DimCustomer table from the AdventureWorksDW table which serves as the birthdate column. I've eliminated columns that aren't necessary so that it is easier to understand.

For you to calculate your age for each consumer the only thing you'll need is:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; start by selecting the Birthdate column.
• click on the Add Column Tab, and choose the "From Date & Time" section. Next, click on Date, select the appropriate age.

That's it. This does calculate any variation from the Birthdate column, as well as the current date and time.

However, the age that shows up in the Age column, doesn't really look like an age. It is due to the fact that it is a Duration.

Duration

Duration is an individual type of data type that is used in the data type used in Power Query that represents the distinctions between the DateTime and DateTime values. Duration is a mixture with four numbers.

days.hours.minutes.seconds

This is the best way to understand the above numbers. However, from a user's standpoint, they shouldn't need to search for information like those. There are methods by which you are able to determine each of the segments that are durational. If you go to the Duration menu you'll find that you can get the number of seconds to minutes, hours, days, and years from it.

For calculating the age in years for instance, it's simple to calculate Total Year:

The duration is calculated in terms of days, then that divided by the number of days to get an annual figure.

Rounding

Truth is, no one claims one's age was 53.813698630136983! They use the term 53 and then they round it down. It is possible to select the Rounding option and then round down option from the Transform tab.

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

Clear the other columns If you wish (or there could be that you used transformations with the Transform tab to avoid making new columns), and call this column"Age:

Things to Know

• Refresh The date calculated this way will get refreshed at the time of refreshing your dataset. Each time, the system will evaluate dates of birth and date and timing for the refresh. This method is the initial calculation of the age. If you need the calculation to be automatically done with DAX This is the approach I've described how to use.
• Why Power Query: Benefits of making age calculations with Power Query is that the calculation takes place at the time of refreshing your report. Your report will be refreshed with a tool that makes calculation simpler, and there's no added cost of making it through DAX as measure of runtime.
• Another possibility is that it is not meant to calculate the age of a person solely by their birthdate. This can be used to calculate the quantity of inventory for products as well as the different between two dates or times from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He is an BSc from Computer engineering. He holds greater than twenty years of experience in the field of data analysis as well as BI, databases programming, and development predominantly using Microsoft technologies. He was a Microsoft Data Platform MVP for nine years (from 2011 until the present) due to his commitment in Microsoft BI. Reza is a prolific blogger, as well as the editor and co-founder of RADACAD. Reza is also the co-founder and organizer of Difinity event which takes place 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 other books. He was also a frequent member of online forums on technical questions like MSDN and Experts-Exchange as well as moderator on the MSDN SQL Server forums, and is an MCP and MCS as well as an MSCITP of the BI. He is also the head of the New Zealand Business Intelligence users group. He is also the creator of the loved by many. Power BI from Rookie to Rock Star It is available for free and contains more than 1700 pages worth of information and a second book called Power BI Pro Architecture published by Apress.
His qualifications include being 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 love is helping users find the perfect data solution. He is a Data enthusiast.This post was filed in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed in Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Please leave a comment.

Post navigation

- Share different visual pages with different Security Groups Power BIAge's years Calculation is able to calculate Leap Year in Power BI using Power Query