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 a new column with DATEDIF Function. For ilustration I attached the picture and table1 code. Number of days(JudgementDelayDays) to the current day are calculated in the blue circle. I was thinking to calculate it from these values in the blue circle... The second idea is to use start date(DefinitiveJugmentDay) from the date when the case was created to the current date(slicer)....

 

 
 
The result should look like this, in this format....below 
 

 

 
Any help most welcome. 
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.

     

     

     

     

     

     

3 Replies

  • MNedix's avatar
    MNedix
    Solution 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

     

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hello v-yajiewan-msft and MNedix,

       

      Thank you for your interest. This solution works like a charm. Thank you.