What is Calorie Counting?

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. However, as DAX is the most used language usedin several calculationsin the Power BI platform, many don't have any idea about the function that is available in Power Query. In this blog article, I will explain how easy to calculateAge in Power BI using Power BI. The methodis extremely helpful in situations where computation of an agecan be performed using an earlier calculated row by row basis.

Calculate Age from a date

It's the DimCustomer table that is part of the AdventureWorksDW table that has the birthdate column. I've removed columns that aren't required to make it more user-friendly;

To calculate what is the average age each buyer, all you need to do is:

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

That's all there is to it. It will calculate an amount that is the sum that is the sum of Birthdate column, and also the current time and date.

However, the age that appears in the Age column, doesn't look like an age. This is because it's an actual time period.

Duration

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

days.hours.minutes.seconds

This is how you look at the above information. However, for an individual's perspective you don't want them to find facts like this. There are methods that can determine each component that is an amount of time. If you choose the Duration menu , you'll be able to see that you'll be able extract the number of seconds, minutes of hours, days, or years out of it.

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

Be aware that the duration of your program will be measured in days , and then divided into 365 to provide you with the annual value.

Rounding

There is no way to prove they are 53.813698630136983! They use the term 53, then they round it down. It's easy to choose Rounding , and Round Down in the Transform tab.

This will provide you with the age in years:

It is then possible to clean other columns if you'd like (or maybe you've made use of transformations on the Transform tab to prevent the making of columns) This column may be changed to an age: column or Age:

Things to Know

• Refresh The age that is calculated using this method will be updated each time you refresh your database. Every time, the database will be compared the birthdate with the date and time for the refreshing. This method is an initial calculation of the age. If you're looking for your calculation performed dynamically with DAX, I've provided a method you can apply.
• The motive behind Power Query: Benefits of performing age calculations in Power Query is that the calculation takes place when you refresh your report. You use an application that makes the calculation more efficient, and there's no added cost of doing it with DAX to determine the runtime.
• Another case where these are not for calculating age only starting from the birth date. This can be used to determine the age of inventory for products as well as for the variation between two dates or dates 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 the more than 20 years of knowledge in the field of data analysis data and BI, database development and programming primarily on Microsoft technologies. He has been an Microsoft Data Platform MVP for nine consecutive years (from 2011 until today) for his commitment for Microsoft BI. Reza is an avid writer and co-founder of RADACAD. Reza is also co-founder and co-organizer at the 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 a few publications on MS SQL BI and also is working on a few more. In addition, he was a frequent member of online technical forums such as MicrosoftDN as well as Experts-Exchange and was also the moderator of MSDN SQL Server forums, and is an MCP or MCSE. He also holds the MCITP for BI. He is the creator of the New Zealand Business Intelligence users group. In addition, he is the creator of the popular workbook Power BI from Rookie to Rock Star, which is free with more than 170 pages of material as well as it 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 main goal is helping users find the right data solution. He is a Data enthusiast.This article was published in Power BI, Power BI from Rookie to Rockstar, Power Query and is covered with Power BI, Power BI from Rookie to Rock Star, Power Query. The following is apermalink.

Post navigation

Create different visual pages and share them with different security groups within Power BIAge in Years Calculation which can be used as a way to calculate Leap Year in Power Business Intelligence by using Power Query