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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Super User
Super User

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

Anonymous
Not applicable

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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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
Super User
Super User

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!

Anonymous
Not applicable

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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