cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

7 REPLIES 7
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?

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

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.

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

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

Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors