power-converter

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. But, because DAX is the most widely used language usedin several calculationsin Power BI, many are unaware of this function in Power Query. In this blog post I will describe how simple to calculateAge in Power BI with Power BI. This methodis highly beneficial in situations where computation of an agecan be done using an earlier calculated row by row basis.

Calculate Age from a date

This is the DimCustomer table in the AdventureWorksDW table that has the birthdate column. I've removed columns that don't need to be there in order to make it easier to read;

For you to calculate how old each purchaser, the following information is required:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; make sure to select the first column in the Birthdate column.
• Click on the Add Column Tab. Under "From Date & Time" section, and then under Date choose the date range.

That's about it. This calculates the calculate the amount which is the total of both the Birthdate column, as well as the current time and date.

But, the age that appears within the Age column does not look like an age. It is due to an actual length.

Duration

Duration is a specific type of data used for Power Query which represents the distinction between two DateTime values. Duration is a combination of four numbers:

days.hours.minutes.seconds

This is how you read the above data. But, from an individual's perspective you don't want them to find details like this. There are methods that can determine each component that is equivalent to the duration. If you select the Duration menu you'll notice that you'll be able to get the amount of seconds or minutes or hours, days, and years from it.

For aid to assist calculating the age in years such as, say, it is easy to select Total Years.

Note that the length that the programme runs is calculated by days and then subdivided into 365 to provide you with the annual figure.

Rounding

Finally, no one says they're 53.813698630136983! They call it 53, and then then round it down. It is easy to select rounding as well as Round Down under the Transform tab.

This will provide you with the age in years:

It's also possible to remove other columns if you'd like (or maybe you've made use of transformations in the Transform tab to prevent the development of new columns) This column can be renamed to Age: column (Age:

Things to Know

• Refresh The age calculated by this method will be updated every time you refresh your database. Each time, it will compare the birthdate to the date and the time when the update was completed. This method is an earlier calculation of the age. If you require this calculation performed dynamically with DAX, I've explained a way that you could apply.
• The reason behind Power Query: Benefits of doing calculations on age in Power Query is that the calculation takes place at the time of refreshing your report. You use a tool that makes the calculation easier, and there's no cost of doing it with DAX for calculating the time of runtime.
• Another scenario These are not in use to calculate age, only beginning at the date of birth. It could be used to calculate the age of inventory for products , and also for the difference 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 with a degree in Computer engineering. He holds over 20 years' knowledge in the field of data analysis data in BI, databases, and programming mostly using Microsoft technologies. He has been a Microsoft Data Platform MVP for 9 consecutive years (from 2011, until now) in recognition of his commitment for Microsoft BI. Reza is an avid author and co-founder of RADACAD. Reza is also co-founder as well as coordinator 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 written some ebooks concerning MS SQL BI and also is writing some others, The author was a regular forum member on technical forums on the internet like MicrosoftDN as well as Experts-Exchange and was the moderator on the MSDN SQL Server forums, and is an MCP or MCSE. He also holds the MCITP for Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. Additionally, he's the creator of the well-known book Power BI from Rookie to Rock Star, which is available for free and has more that 170 pages of information and portion 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 people find the right data solution. He's a Data enthusiast.This entry was written by Reza within Power BI, Power BI from Rookie to Rockstar, Power Query and is classed with Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

Use different visual pages to share with various security groups inside Power BIAge in Years Calculation which is able to calculate Leap Year in Power BI with the help of Power Query