Dax Calculation for DateDiff

Dear all,

I would like to create date difference for 2 dates (in days or hours) and I understand the DAX formula is DATEDIFF.

As you can see,

  1. The very first start date is 3/13/2024 and the very last finish date is 3/21/2024.
  2. The work order is segregated into different operation steps, each with their own start date and finish date.

Is there a DAX formula I can use to calculate the datediff of the whole work order?
Eg, DATEDIFF of 3/13/2024 and 3/21/2024 = 8 days

Right now, DATEDIFF will only give me results of each individual operation steps (which I also need) but it will not give the results of 8 days.

Thank you.

Operation Sample.pbix (56.2 KB)
Operation Sample.xlsx (12.9 KB)

1 Like

See if it helps

Date_Dif_Order = 
VAR Var1 =
        MIN ( Table1[ActualStartDate] ),
        ALLEXCEPT ( Table1, Table1[Work Order] )
VAR Var2 =
        MAX ( Table1[ActualFinishDate] ),
        ALLEXCEPT ( Table1, Table1[Work Order] )
    DATEDIFF ( Var1, Var2, DAY )

1 Like

Check out this thread from Data Mentor explaining how this formula works

1 Like

Thanks so much Vilmar! It works great!

May I also ask what about if I want to see the difference between each Operation steps?
The current DAX can show difference from start and finish of each step, but not the difference between 2 steps.

My apologies for not asking this earlier.

Thank you Sam! This post is very educational.

The difference between step 2 and 1 is 166 seconds. Would that be it?


Dif Sec = 
    MAX ( Table1[ActualStartDate] ) + MAX ( Table1[ActualStartTime] ),
    MAX ( Table1[ActualFinishDate] ) + MAX ( Table1[ActualFinishTime] ),
Dif Betw Steps (Sec) =
IF (
    MAX ( Table1[Operation Steps] ) <> 1,
    [Dif Sec]
        - CALCULATE (
            [Dif Sec],
            WINDOW ( -1, REL, -1, REL, ALLEXCEPT ( Table1, Table1[Work Order] ) )
1 Like

Yes! Thanks so much!