Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, as DAX is the most popular language usedin several calculationin the Power BI platform, many don't know about this feature in Power Query. In this post, I will explain how easy it is to calculateAge in Power BI using PowerBI. This methodis extremely helpful in cases where the calculation of agecan be completed on a previous calculated row-by-row basis.

Calculate Age from a date

It's the DimCustomer table in the AdventureWorksDW table which contains an age column. I've taken out some columns that aren't needed for clarity;

To calculate an age for each buyer, you need is:

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

It's that simple. it. It will calculate an amount that is the total of both the Birthdate column as well as the current date and time.

However, the date that appears in the Age column, does not really appear like an age. It is due to an actual duration.

Duration

Duration is a particular type of data utilized to calculate the duration of a query in Power Query which represents the differences between the two DateTime values. Duration is the sum of four values:

days.hours.minutes.seconds

This is how you view the above statements. But, from one's own perspective, you don't want them to seek data like this. There are methods that can extract each segment that's equivalent to the duration. When you go to the Duration menu you'll discover that you'll be able to get the amount of seconds or minutes or hours, days, and years out of it.

Help in calculating the age in years such as, for instance, it is easy to select Total Years.

Be aware that the duration of the program is measured in days , and then divided into 365 so that you can come up with the annual value.

Rounding

No one claims they are 53.813698630136983! They use the term 53 and then round it down. It is easy to select Rounding and Round Down by clicking on the Transform tab.

This will show you your age in years:

It's then possible to tidy other columns as you'd like (or perhaps you've used the power of transformations within the Transform tab to stop the making of columns) The column could be renamed as Age column or Age:

Things to Know

• Refresh The age calculated by this method is updated every time you refresh your database. and each time will compare the birthdate to the date and the date that the database refresh was made. This method is an initial calculation of the age. If you require that the calculations be run dynamically by DAX, I have described a method that you can apply.
• The reason behind Power Query: Benefits of performing age calculations in Power Query is that the calculation takes place at the time of refreshing your report, using tools that make the calculation easier, and there's no cost to doing it using DAX to measure the time of runtime.
• Another situation is to be used for age calculations, but only start with the birth date. This could be used for the time of inventory on products and also the different of two date or times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc at the level of Computer engineering. He holds an extensive 20-year' experience in the field of data analysis data, BI, databases developing, and programming mostly on Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years consecutively (from 2011 to the present) in recognition of his commitment to Microsoft BI. Reza has been an active writer and co-founder at RADACAD. Reza is also co-founder and organizer of Difinity Conference which is held 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 has also written a few books regarding MS SQL BI and also is working on other books. The author was a frequent forum member on technical forums on the internet like MSDN , and Experts-Exchange and was the moderator on the MSDN SQL Server forums, and holds the MCP or MCSE. He also holds the MCITP for Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. He is also the co-author of the popular book Power BI from Rookie to Rock Star, which is completely free and includes more than 170 pages of information and is a integral part of 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 User Groups. And He is a Microsoft Certified Trainer.
Reza's dream is to assist users find the right data solution. He is a Data enthusiast.This entry was posted within Power BI, Power BI from Rookie to Rockstar, Power Query and is listed within Power BI, Power BI from Rookie to Rock Star, Power Query. Bookmark the permalink.

Post navigation

Share different visual pages with different security groups in Power BIAge in Years Calculation which is able to calculate Leap Year in Power BI using Power Query