Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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🙂
Solved! Go to Solution.
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! 😀
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
Hi @Anonymous how did you solve this question?
@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.
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! 😀
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.
User | Count |
---|---|
128 | |
73 | |
70 | |
58 | |
53 |
User | Count |
---|---|
193 | |
96 | |
66 | |
62 | |
52 |