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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

 

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.