DATEDIF Function years, months, and days between dates

[Anonymous]

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. 

[Anonymous · Accepted solution]

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]

Hello v-yajiewan-msft and MNedix,

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

[MNedix]

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.