Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way to calculating the age. However, as DAX is the primary languagein several calculationin Power BI, many do not realize this feature of Power Query. In this article, I am going to show you how easy is to calculateAge in Power BI using PowerBI. The methodis extremely useful in situations where an computation of an agecan be done on a pre-calculated row by row basis.

Calculate Age from a date

Below is the DimCustomer table that is part of the AdventureWorksDW table, which acts as a birthdate column. I have removes some of the extra columns to make it easier to read.

For you to calculate your age for each consumer, all you have to do is to:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; choose the first column, Birthdate.
  • Go to the add Column Tab, which is under the "From Date & Time" section, and then under Date, choose Age

That's all there is to it. this will calculate any difference in the Birthdate column along with the current date and time.

However, the age you see on the Age column doesn't actually appear to be an actual age. That is because it is a duration.

Duration

Duration is a particular type of data in Power Query which represents the differences between two DateTime values. Duration is a mixture of four numbers:

days.hours.minutes.seconds

This is how you can read the above values. For users' perspective you shouldn't expect them to go and read the information like the ones above. there are ways that you are able to get each segment that is the amount of time. Utilizing the Duration menu, you will see that you can get the amount of seconds to minutes, hours days and years out of it.

To assist in calculating the age in years for instance the age of a person, simply click on Total Years:

The duration is calculated in days . It is after that divided by the number of days, to give you the annual value.

Rounding

At the final point, no one is claiming their age as 53.813698630136983! They refer to it as 53, then rounded down. You can easily choose the Rounding option and then round down on the Transform tab for it.

This will give you the age in years:

Then you can tidy the other columns, if you wish (or there is a chance that you have made use of transformations on the Transform tab to prevent any creation of columns) And name this column Age.

Things to Know

  • Refresh The data's age calculated this way will be refreshed at the time of refreshing your dataset. Every time, the system will be able to compare dates of birth and the date and timing of the refresh. This method is an earlier calculation of age. If you, however, require the calculation of age to be dynamically done using DAX, here I explained the method you could make use of.
  • The reason behind Power Query: Benefits of using age calculation with Power Query is that the calculation is performed when you refresh your report. You use an algorithm that makes the calculation more simple, and there's no added cost to calculate it using DAX as a measure runtime.
  • Different scenarios The Other scenarios are not a method to calculate age just from birthdate. It can be used to calculate product inventory and for the variation between two dates and dates from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has been awarded a BSc in Computer engineering. He holds more than 20 years of experience in the field of data analysis and BI, databases, development, and programming mainly with Microsoft technologies. He is a Microsoft Data Platform MVP for nine consecutive years (from 2011, until now) due to his dedication to Microsoft BI. Reza is a frequent author and co-founder at RADACAD. Reza is also co-founder as well as co-organizer of Difinity conference 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 books on MS SQL BI and also is working on other books. He was also an active participant in online forums for technical issues like MSDN and Experts-Exchange and was also a moderator of MSDN SQL Server forums, and holds the MCP and MCSE as well as an MCITP of BI. He is the director in the New Zealand Business Intelligence users group. Also, he's the writer of the well-known book Power BI from Rookie to Rock Star, which is available for free and has more that 1800 pages of material and the Power BI Pro Architecture published by Apress.
A.J. 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 love is to help users find the best data solution. He is a Data enthusiast.This article was published in Power BI, Power BI from Rookie to Rockstar, Power Query and tagged Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great resource for you to bookmark.

Post navigation

Share Different Visual Pages by using Different Security Groups in Power BIAge's Calculation of Years that is used for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

All Vegetables Name In English

  All Vegetables Name   In English-Hindi   Vegetable Name   (sbjiyo ke naam) All Vegetables Name  In English-Hindi  Vegetable Name  (sbjiyo ke naam).   Friends We all know that everyone eats vegetables.   There's no one who doesn't.   Vegetables are the primary ingredient in many dishes and are extremely beneficial to our health. There are a myriad of  vegetables name  that are grown and eaten across the world.   We are familiar with the names of  several  kinds of vegetables, but we're not aware of names for the various varieties.   This is because certain types of vegetables are restricted to specific regions or countries and others are found everywhere in the world. Children in the first grade or in the second or the first grade  expected  to be familiar with the names of the vegetables from the very beginning.   This will assist them in learning the fundamentals of vegetables name in engli...
Read more

Birds Name in english

  Birds Name in english Birds Name in english  and Hindi with  Birds Name  in Sanskrit Birds Scientific Name Here you can discover the bird's name and the names of the bird in Hindi as well as English.   For more information details about the name of the bird as well as their image, read on. Are you a big fan of birds?   Do you like birds?   I'm sure it's because of the fact that you  enjoy  the birds extremely similar to me.   There's no one on the planet who doesn't love birds since they're beautiful and adorable that draw the attention of anyone who sees them. Birds also play an important role to the environment.   It doesn't matter if  it's an outdoor garden, house on the roof or in the open air, birds will be observed in the area.   The tweets of these birds, they are flying through the air flying through gardens and roofs, and producing beautiful sound.   It's truly relaxing and enjoyable feeling.   If ther...
Read more