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

Get inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code FABINSIDER for a $400 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors