Forum Discussion
Helper IHow to Calculate Age dynamically
Greetings Community,
I have a table that contains Employee Number, Hire Date, Date of Birth and Age. The "Age" column reflects the age as of today i.e. Current Date - Date of Birth
I have added a date slicer with year values and i want to be able to calculate the age of the employee at a certain point of time depending on the year selected in the slicer (the year in the slicer is coming from a Date Dimension Table)
For e.g. IF an employee's age is 30 years old as of today , if i select 2018 from the slicer the age should be 28, The purpose of this is a dashboard i am building to show the age ranges of employees hired in certain years.
I am assuming I would have to use some meausres but as I am fairly new to Power BI , i would appreciate any guidance in the correct way.
Thank you in advance.
Hi melhajj ,
Refer this measure.
Measure = var a = YEAR(TODAY())-YEAR(SELECTEDVALUE('Table'[Date of Birth])) return IF(ISFILTERED(slicer[year]),a-(YEAR(TODAY())-SELECTEDVALUE(slicer[year])),a)Result would be shown as below.
Best Regards,
Jay
5 Replies
- amitchandak
Super User
melhajj , Create a date table and use year from it.
age =
var _max = maxx(allselected(Date),Date[Date])
return
datediff(min(Table[DOB]),_max,YEAR)To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos. 
melhajjamitchandak az38 thank you for your guidance and replies. I am actually trying to do something a bit more complicated. As per the image i attached , I am trying to group the employees in age groups accorsding to the year i am selecting. So in the image, the chart represents how many people where in the company at a certain year. in this image , i am using the "Age" fieled i had already mentioned (which is wrong because AGE is the age as of today) , I do know now that i have to use a measure to calculate the age depending on the year selected but how can i do the age grouping ? Appreciate any guidance.
Hi melhajj ,
Refer this measure.
Measure = var a = YEAR(TODAY())-YEAR(SELECTEDVALUE('Table'[Date of Birth])) return IF(ISFILTERED(slicer[year]),a-(YEAR(TODAY())-SELECTEDVALUE(slicer[year])),a)Result would be shown as below.
Best Regards,
Jay
I am also trying to calculate age dynamically for solar sites with their date of completetion. I want to age to refect based on the slicer year selection. In the below formula , I am not able able understand what is the "slicer[year] and "a" . my tables has only 2 columns site name and date for completetion. please help , i m new to power BI
Measure = var a = YEAR(TODAY())-YEAR(SELECTEDVALUE('Table'[Date of Birth])) return IF(ISFILTERED(slicer[year]),a-(YEAR(TODAY())-SELECTEDVALUE(slicer[year])),a)
