Age Calculation in Power BI using Power Query

Power Query has a simple way of calculating the age. However, because DAX is the popular language in many calculations in Power BI, many are not aware of this function in Power Query. In this video, I am going to show you how easy is to calculate Age in Power BI using Power BI. This method is very useful when the age calculation can be done as a pre-calculated row by row basis.
Read my blog article for more details and links to study more:
radacad.com/ag...

[@tjiss7094]

So useful especially for beginners of powerbi

[@mohammadaamirkhan7830]

Sound Interesting! Thanks for making an insightful video.

[@charlenemarch3997]

WOW this was a Huge time saver. Thank you! great video.

[@RADACAD]

Glad it helped!

[@ssma1368]

Another interesting tutorial! Thanks Reza🙏

[@charlusmissier6135]

Thanks a lot. The video solved a huge question I had

[@PLBL]

Hey. What if you have a date slicer on your dashboard and you need the age at a point in time? (e.g. I select the year 2013 and want to see their age distributions then). This method only gives the age they are right now.

[@pieterjdw]

Thanks! How would you create a frequency representation of the age. Would be even beter if you present it in age brackets

[@stephaniewilson537]

Just what I need to know...thank you!!

[@mukkerasrikanth4848]

Thank you for the clarification

[@isranim]

How to write query when you have to calculate ageing in days... For example there is a "Last working date" Is blank, and you have "date of joining" Like we write in excel = if(lwd) = "", today() - (doj), (lwd) -(doj) Similarly I have to find out the number of days people spent at workplace.

[@PLBL]

Hey! What if you need the age to calculate dynamically based on a slicer on your dashboard. For example you have a slicer which changes the financial year of your report, and you have a graph showing age brackets of your workforce or something like that. You need the calculation find their age in that financial year, not their age today.

[@RADACAD]

In that case, you need to calculate age through DAX. I have blog and video about that too

[@signeflink27]

@@RADACAD Hello. Could you please add link. Thanks advance!

[@ashoklingutla2259]

How to calculate age in direct query mode

[@akramhemmat550]

Hi Dear, I need Formula for deferent ages E.g. : 0-5 , 6-18, 19-49, 50-60, 60-70, I need this formula to add it in the custom column when I am adding new custom column. Thanks.

[@akramhemmat550]

Finally I found it

[@khurrammansoor9805]

Very Nice thank you

[@jeremyjerez5811]

awesome! thanks a lot!!

[@mathew9665]

As you note in a reply to Peter's comments - Do you have a solution that correctly deals with Date of Birth age calculations and the Leap Year issue?

[@RADACAD]

The leap year issue is not a major issue of course, however, it can be easily fixed with a few more steps. let me first give you an example, so that you can understand what the issue is: let's say we have someone born on 1990, July 14th. and right now is Jan 2020. The age calculation returns 29, which is correct, even though we have leap years in between. what if that person born on 29th of Feb? then the same, age calculation would be right. The age calculation would be wrong only when the difference of the birthdate with the current date is less than or equal to the number of leap years between the two dates. for example, if someone born on 18th of Jan 1990, and we are calculating the age on 18th of Jan 2020, the age is 30, which is right. but if someone born on 25th of Jan 1990, even though today is 18th of Jan 2020, still it shows age as 30, because there are about 7 leap years, equivalent of 7 days in our calculation in between those dates. so as you can see, only with a few changes and adding a few more steps, you can get the calculated precisely if you want. I might do another video about it.

[@DaxtasticNL]

I would love to see the solution for Power Query also. In my opinion in Power Query it always goes wrong with calculations on ages with a birthdate where the day of birth is around the actual day of today. (see the posted picture in my previous comment). So for example someone was born on the 17th of january in a year and today it is the 17th of January 2020. So when you today check out the birthdates directly before or after the 17th of January of any year, there would be some wrong age calculations. If you have a solution in Power Query that would work perfectly, that would be great!

[@RADACAD]

@@DaxtasticNL Hi Peter. Saying that "Power Query is always wrong with age" is not a correct analysis. Language is never wrong. we should understand what the function is doing, and if the function is not doing what it supposed to do, then yes, it is wrong. In our case; the TotalYears is just duration in days divided by 365, nothing more, nothing less. and it is calculating that value as expected. However, for calculating age in years, you expect some other considerations (the leap year is one example), in that case, if the language doesn't have the function for it, we can write one for it. No problem. and it is not complicated. I will post one soon, and you can see that it is working.

[@RADACAD]

Here is how you can do it: kzbin.info/www/bejne/aGe6d3Z5rLtpocU

[@DaxtasticNL]

Hi Reza, I understand what you are saying, but in my opinion the "age-function" / "age-button" is not doing what I expect it would be doing. It's not giving me the age, but it is giving me a duration in days/minutes/hours/seconds. That's confusing in my opinion because that is not an age. But thanks again for your new great post and your quick solution for the "problem" 👍

[@isranim]

How can we put this age slab... Say 0 to 20 has 2 people, 20+ to 40 has 4 people etc

[@RADACAD]

You can use Group By transformation on a calculated column which is used for bands

[@sharatbabu7228]

Hi Sir, can u tell how can we calculate the upcoming birthdays in current week and next week.

[@laurentiuionutvita]

thanks

[@RADACAD]

You're welcome!

[@souhaildahmeni9961]

Thanks

[@krishanakarmogili2303]

I looking to group the age into 3 categories like 60, could you please share the link if you have

[@RADACAD]

You can do that with adding a conditional column

[@aliazad1118]

Perfect as usual...