Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I have a table that holds a list of Employees that has fields like:
Employment Id
Gender
Ethnicity
Date of Birth
Employment Start Date
Employment End Date
I also have a Calendar table that will show dates from the First Employment Start Date up to Todays Date, which I am joining the Date fields like Date of Birth, Employment Start Date, Employment End Date to the Date field on Calendar.
I want to know the Age of an Employee in Years, so I have used the DATEDIFF Dax function as per below:
But if I select a Year from the calendar table lets say 2020 well I want to know what the Employees Age was back then?
I tried the following DAX Measure but it shows an Error Message
Years =
Is this possible?
Thanks
Solved! Go to Solution.
Hi @PowerAuto82 ,
You can update the formula of measure [Age] as below and check if it can return the correct result... Please find the details in the attachment.
Age =
VAR _selyear =
SELECTEDVALUE ( 'Calendar'[Date].[Year] )
VAR _selbirthdate =
SELECTEDVALUE ( 'Employee'[Birth Date] )
VAR _bmonth =
MONTH ( _selbirthdate )
VAR _bday =
DAY ( _selbirthdate )
VAR _tmonth =
MONTH ( TODAY () )
VAR _tday =
DAY ( TODAY () )
VAR _age =
DATEDIFF ( _selbirthdate, DATE ( _selyear, _tmonth, _tday ), YEAR )
RETURN
SWITCH (
TRUE (),
_bmonth > _tmonth
|| ( _bmonth = _tmonth
&& _bday >= _tday ), _age,
_bmonth < _tmonth
|| ( _bmonth = _tmonth
&& _bday < _tday ), _age - 1
)
Best Regards
I have changed your code to the below, i think this might have worked
Age =
VAR _selyear =
SELECTEDVALUE ( 'Calendar'[Date].[Year] )
VAR _selbirthdate =
SELECTEDVALUE ( 'Employee'[Birth Date] )
VAR _bmonth =
MONTH ( _selbirthdate )
VAR _bday =
DAY ( _selbirthdate )
VAR _tmonth =
MONTH ( TODAY () )
VAR _tday =
DAY ( TODAY () )
VAR _age =
DATEDIFF ( _selbirthdate, DATE ( _selyear, _tmonth, _tday ), YEAR )
RETURN
SWITCH (
TRUE (),
_bmonth > _tmonth
|| ( _bmonth = _tmonth
&& _bday > _tday ), _age - 1,
( _bmonth = _tmonth
&& _bday = _tday ), _age,
_bmonth < _tmonth
|| ( _bmonth = _tmonth
&& _bday < _tday ), _age
)
Hi @PowerAuto82 ,
First, if you want to get the age dynamically base on the slicer selection, you need to create a measure rather than a calculated column. Please review the following links to check the difference of measure vs calculated column...
Calculated Columns and Measures in DAX
Calculated Columns vs Measures
Then you can create a measure as below:
Age =
VAR _selyear =
SELECTEDVALUE ( 'Calendar'[Date].[Year] )
VAR _selbirthdate =
SELECTEDVALUE ( 'Employee'[Birth Date] )
RETURN
DATEDIFF ( _selbirthdate, DATE ( _selyear, 12, 31 ), YEAR )
Best Regards
Hi @v-yiruan-msft , Thanks for this, it looks good but it is not showing the correct Age for the current Year we are in?
For example if the calendar year is selected on 2022 and the employee was born on 01/09/1992 (dd/mm/yyyy) then it will show the correct age of 30, but if the employee was born on 01/10/1992 (dd/mm/yyyy) they will also show the age of 30 but really they are 29 as there birthday has not come yet.
Thanks
Hi @PowerAuto82 ,
You can update the formula of measure [Age] as below and check if it can return the correct result... Please find the details in the attachment.
Age =
VAR _selyear =
SELECTEDVALUE ( 'Calendar'[Date].[Year] )
VAR _selbirthdate =
SELECTEDVALUE ( 'Employee'[Birth Date] )
VAR _bmonth =
MONTH ( _selbirthdate )
VAR _bday =
DAY ( _selbirthdate )
VAR _tmonth =
MONTH ( TODAY () )
VAR _tday =
DAY ( TODAY () )
VAR _age =
DATEDIFF ( _selbirthdate, DATE ( _selyear, _tmonth, _tday ), YEAR )
RETURN
SWITCH (
TRUE (),
_bmonth > _tmonth
|| ( _bmonth = _tmonth
&& _bday >= _tday ), _age,
_bmonth < _tmonth
|| ( _bmonth = _tmonth
&& _bday < _tday ), _age - 1
)
Best Regards
Hi @v-yiruan-msft Thanks for latest reply, I have opened it but the age does not look right still
But the Ages should be the below when the Year 2022 is selected
Thanks
I have changed your code to the below, i think this might have worked
Age =
VAR _selyear =
SELECTEDVALUE ( 'Calendar'[Date].[Year] )
VAR _selbirthdate =
SELECTEDVALUE ( 'Employee'[Birth Date] )
VAR _bmonth =
MONTH ( _selbirthdate )
VAR _bday =
DAY ( _selbirthdate )
VAR _tmonth =
MONTH ( TODAY () )
VAR _tday =
DAY ( TODAY () )
VAR _age =
DATEDIFF ( _selbirthdate, DATE ( _selyear, _tmonth, _tday ), YEAR )
RETURN
SWITCH (
TRUE (),
_bmonth > _tmonth
|| ( _bmonth = _tmonth
&& _bday > _tday ), _age - 1,
( _bmonth = _tmonth
&& _bday = _tday ), _age,
_bmonth < _tmonth
|| ( _bmonth = _tmonth
&& _bday < _tday ), _age
)
Hi @PowerAuto82 ,
Thanks for your feedback. If your problem has been resolved, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.
Best Regards
Hi @PowerAuto82 ,
Try something like this below.
DateDiff Test =
VAR vDOB =
YEAR (
CALCULATE (
FIRSTNONBLANK ( 'Table'[Date Of Birth], 1 ),
REMOVEFILTERS ( 'Date Table'[Year] )
)
)
VAR vSelectedYear =
YEAR (
CALCULATE ( MAX ( 'Date Table'[DateKey] ), ALLSELECTED ( 'Date Table'[Year] ) )
)
VAR vDateDiff =
DATEDIFF ( DATE ( vDOB, 01, 01 ), DATE ( vSelectedYear, 12, 31 ), YEAR )
RETURN
vDateDiff
Hope this helps.
Did I help you today? Please accept my solution and hit the Kudos button.
Hi @davehus, Thanks for this, it looks good but it is not showing the correct Age for the current Year we are in?
For example if the calendar year is selected on 2022 and the employee was born on 01/09/1992 (dd/mm/yyyy) then it will show the correct age of 30, but if the employee was born on 01/10/1992 (dd/mm/yyyy) they will also show the age of 30 but really they are 29 as there birthday has not come yet.
Thanks
In the datediff change Date(vSelectedYear, 01,01)
To Date(vSelectedYear, Month(TODAY()), DAY(TODAY))) and see if they works?
Hi @davehus I have tried the above suggesting
BEFORE
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
84 | |
66 | |
49 |
User | Count |
---|---|
140 | |
114 | |
108 | |
64 | |
60 |