Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
PowerAuto82
Helper II
Helper II

Dynamic Calculate Age at point of time date selection

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

 

2 ACCEPTED SOLUTIONS

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
    )

yingyinr_0-1663896158247.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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
    )

View solution in original post

10 REPLIES 10
v-yiruan-msft
Community Support
Community Support

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 )

yingyinr_0-1663838986884.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
    )

yingyinr_0-1663896158247.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yiruan-msft Thanks for latest reply, I have opened it but the age does not look right still

 

PowerAuto82_0-1663918192440.png

But the Ages should be the below when the Year 2022 is selected

 

PowerAuto82_1-1663918273953.png

 

Thanks

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
    )

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
davehus
Memorable Member
Memorable Member

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

 

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 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.