Dynamic Calculate Age at point of time date selection

[PowerAuto82]

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:

Age =

        DATEDIFF('Employee'[Birth Date],TODAY(),YEAR)

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 =

Age =

        DATEDIFF('Employee'[Birth Date],VALUES(Calendar[Date]),YEAR)

Is this possible?

Thanks

[v-yiruan-msft · Accepted 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

[v-yiruan-msft]

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

[PowerAuto82]

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

[v-yiruan-msft · Accepted 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

[PowerAuto82]

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

[PowerAuto82 · Accepted solution]

Hi @v-yiruan-msft 

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
    )

[v-yiruan-msft]

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

[davehus]

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.

[PowerAuto82]

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

[davehus]

In the datediff change Date(vSelectedYear, 01,01)

To Date(vSelectedYear, Month(TODAY()), DAY(TODAY))) and see if they works?

[PowerAuto82]

Hi @davehus I have tried the above suggesting

BEFORE

VAR vDateDiff =

   DATEDIFF ( DATE ( vDOB, 01, 01 ), DATE ( vSelectedYear, 12, 31 ), YEAR )

AFTER

VAR vDateDiff =

    DATEDIFF ( DATE ( vDOB, 01, 01 ), DATE ( vSelectedYear, MONTH (TODAY()), DAY (TODAY()) ), YEAR )

But the Year id selected for 2022 is showing that they already have had their birthday even though that dates has come yet