DAX to Convert days to Years,months & days

[Anonymous]

Hi Community,

I want to know the DAX to to convert days (a calculated column for difference between two dates) to Years,months & days. For example :

91 days = 3 months 1day

367 days = 1 year 2days

450 days = 1year 2month 26days

I can do it in excel, can't get it right in DAX.

Many thanks🙂

[rpassarelli]

I really like the solution with QUOTIENT, but considering I have the following dates, the result is incorrect

Stard Date: 2020-07-01

End Date: 2023-06-30

Retult: 2 year, 12 months and 4 days

The result should be: 2 year, 11 months and 29 days

[Anonymous]

Hi @Anonymous how did you solve this question? 

[amitchandak]

@Anonymous , Try like a column like

 Switch ( true() ,
		  [days] < 365 = quotient([Days],30) & " Months " & mod([Days],30) & " days " ,
		  quotient([Days],3365) & " Months " & mod([Days],365) & " days " , 
		  [days] & "days"
		  )

[Anonymous]

Hi @amitchandak  is there a way to take day of a year (365/364) into consideration when doing this calculation? I am counting the milestone date and that would make a differnece.

[lkalawski · Accepted solution]

Hi @Anonymous

You can use this DAX to create calculated column:

Converted = QUOTIENT('Table'[Days], 365) & " years " &  QUOTIENT(MOD('Table'[Days],365),30) & " months " & MOD(MOD('Table'[Days],365),30) & " days"

You have to adjust the values you divide by. I don't know if you consider 365 days per year or 366 days. Whether 31 days per month or 30.

_______________
If I helped, please accept the solution and give kudos! 😀

[kpeterson]

This looks like exactly what I need but it is throwing the error, "cannot convert value '30days' of type Text to type Number.  TIA!

[Anonymous]

Hi @lkalawski is there a way to take day of a year (365/364) into consideration when doing this calculation? I am counting the milestone date and that would make a differnece.