Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

DATEDIF Function years, months, and days between dates

Hello ,   I have question how can I calculate in Power BI between start date and current date. My table1 calculates number of days since the start date but I am not able  to work out how to create ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

     

     

     

     

MNedix's avatar
MNedix
Icon for Solution Sage rankSolution Sage
1 year ago

Heya,

I don't fully understand your scenario but you may want to use DATEVALUE in your formulas. Moreover, if you have time in your date then you may want to split the date column in PowerQuery (by Delimiter) and isolate the date (rather than having it also with time). Both of these helped me in the past with date calculations.

 

Hope it helps.