Forum Discussion
DATEDIF Function years, months, and days between dates
Hi Anonymous , hello MNedix, thank you for your prompt reply!
Please try the following measure to display years, months, and days between two dates:JudgmentDelay_YMD_Measure = VAR StartDate = MIN('Table'[DefinitiveJudgmentDate]) VAR EndDate = TODAY() VAR TotalMonthsDiff = DATEDIFF(StartDate, EndDate, MONTH) VAR YearsDiff = INT(TotalMonthsDiff / 12) VAR MonthsDiff = MOD(TotalMonthsDiff, 12) VAR AdjustedStartDate = EDATE(StartDate, YearsDiff * 12 + MonthsDiff) VAR DaysDiff = DATEDIFF(AdjustedStartDate, EndDate, DAY) VAR AdjustedMonths = IF(DaysDiff < 0, MonthsDiff - 1, MonthsDiff) VAR FinalAdjustedStartDate = IF(DaysDiff < 0, EDATE(AdjustedStartDate, -1), AdjustedStartDate) VAR FinalDaysDiff = DATEDIFF(FinalAdjustedStartDate, EndDate, DAY) RETURN YearsDiff & "," & AdjustedMonths & "," & FinalDaysDiffPer my test, I use the Today(2024.10.24) as an example, you could also change it yourself, result for your reference:
Best regards,Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous , hello MNedix, thank you for your prompt reply!
Please try the following measure to display years, months, and days between two dates:
JudgmentDelay_YMD_Measure =
VAR StartDate = MIN('Table'[DefinitiveJudgmentDate])
VAR EndDate = TODAY()
VAR TotalMonthsDiff = DATEDIFF(StartDate, EndDate, MONTH)
VAR YearsDiff = INT(TotalMonthsDiff / 12)
VAR MonthsDiff = MOD(TotalMonthsDiff, 12)
VAR AdjustedStartDate = EDATE(StartDate, YearsDiff * 12 + MonthsDiff)
VAR DaysDiff = DATEDIFF(AdjustedStartDate, EndDate, DAY)
VAR AdjustedMonths = IF(DaysDiff < 0, MonthsDiff - 1, MonthsDiff)
VAR FinalAdjustedStartDate = IF(DaysDiff < 0, EDATE(AdjustedStartDate, -1), AdjustedStartDate)
VAR FinalDaysDiff = DATEDIFF(FinalAdjustedStartDate, EndDate, DAY)
RETURN
YearsDiff & "," & AdjustedMonths & "," & FinalDaysDiff
Per my test, I use the Today(2024.10.24) as an example, you could also change it yourself, result for your reference:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello v-yajiewan-msft and MNedix,
Thank you for your interest. This solution works like a charm. Thank you.