How to Calculate Age dynamically

[melhajj]

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.

[Anonymous · Accepted solution]

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

[prash7882]

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)

[melhajj]

@amitchandak @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.

[amitchandak]

@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-Y...


Appreciate your Kudos.

[az38]

Hi @melhajj 

try a measure

Age on Date = 
VAR SelectedDate = SELECTEDVALUE('Date Dimension Table'[Date])
VAR CurrentEmployee = MAX('Table'[Date of Birth])
RETURN
DATEDIFF(CurrentEmployee, SelectedDate, YEAR)