Dynamic Date where Year and Month from 1 column and the Day from another.

[rush]

I am having issues with calculating age for staff members.

I have a column called : DateList that I want to keep the Year & Month but use the Day part from a different date field.

Please see my current DAX formula below which throws an error "An argument function of date has the wrong data type or the result is too large or too small":

Staff Age = 

VAR Staff_Birthdate = DATE ( YEAR( RELATED( Staff[Birthdate]) ) , MONTH ( RELATED( Staff[Birthdate]) ) , DAY ( RELATED( Staff[Birthdate]) ) )

VAR DateList = DATE ( YEAR( Staff_Headcount[DateList] ) ,  MONTH( Staff_Headcount[DateList] ) , DAY( RELATED( Staff[Birthdate] ) ) )

RETURN

INT ( YEARFRAC (  DateList , Staff_Birthdate ) )

[d_gosbell]

What sort of data type is the column Staff_Headcount[DateList] and what sort of values do you have in there. If it is not a date column calling YEAR() and MONTH() on it could lead to really strange values.

If it's a numeric value like 201906 for june 2019, then a formula like the following should work:

Staff Age = 

VAR Staff_Birthdate = DATE ( YEAR( RELATED( Staff[Birthdate]) ) , MONTH ( RELATED( Staff[Birthdate]) ) , DAY ( RELATED( Staff[Birthdate]) ) )

VAR DateList = DATE ( INT( DIVIDE( Staff_Headcount[DateList] , 100) ) ,  INT( MOD( Staff_Headcount[DateList], 100) ) , DAY( RELATED( Staff[Birthdate] ) ) )

RETURN

INT ( YEARFRAC (  DateList , Staff_Birthdate ) )

[rush]

Hi @d_gosbell 

DateList - Date
Birthdate - Date

The DateList also needs to only change on the Day when the Birthdate Month and Day are the same but it must also take in to account of leap years which makes it complex.

Example:

DateList contains Dates 2013/12/01 , 2014/01/01 , 2014/02/01 etc.

Birthdate contains the actual birth date which I am trying to extract the Day from only when it comes to the birthday month & join that to my DateList field to calculate the age at that point in time.

The VAR DateList is returning an error.

Staff Age = 

VAR Staff_Birthdate = DATE ( YEAR( RELATED( Staff[Birthdate]) ) , MONTH ( RELATED( Staff[Birthdate]) ) , DAY ( RELATED( Staff[Birthdate]) ) ) 

VAR DateList = DATE ( YEAR ( Staff_Headcount[DateList]) , MONTH( Staff_Headcount[DateList]) , DAY( Staff_Birthdate) )

RETURN 

INT ( YEARFRAC ( DateList , Staff_Birthdate ) )

[d_gosbell]

Actually isn't combining the day from one date with the year and month from another is going to cause all sorts of weird, invalid dates like April 31st?

Why couldn't you calculate the age using the DATEDIFF function?

[rush]

@d_gosbell 

Thanks for your help but I won't be able to resolve this issue based on the data I currently have as it will cause a few invalid dates.

I managed to get it working based off my previous calculation.

Thanks.

[v-xuding-msft]

Hi @rush ,

Could you please share more details?  What is the relationship between two tables?  I tried to create a same column to connect the two tables and change the retionship between the tables like the following three scenarios. Then the formula worked.

Best Regards,

Xue Ding

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