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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX to Convert days to Years,months & days

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🙂

 

 

 

 

1 ACCEPTED SOLUTION
lkalawski
Memorable Member
Memorable Member

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"

 

lkalawski_0-1599636005710.png

 

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! 😀

 

 

View solution in original post

7 REPLIES 7
rpassarelli
Regular Visitor

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

cassiec
Frequent Visitor

Hi @Anonymous how did you solve this question? 

amitchandak
Super User
Super User

@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"
		  )

 

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
Memorable Member
Memorable Member

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"

 

lkalawski_0-1599636005710.png

 

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! 😀

 

 

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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