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,
- The very first start date is 3/13/2024 and the very last finish date is 3/21/2024.
- 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 =
CALCULATE (
MIN ( Table1[ActualStartDate] ),
ALLEXCEPT ( Table1, Table1[Work Order] )
)
VAR Var2 =
CALCULATE (
MAX ( Table1[ActualFinishDate] ),
ALLEXCEPT ( Table1, Table1[Work Order] )
)
RETURN
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 =
DATEDIFF (
MAX ( Table1[ActualStartDate] ) + MAX ( Table1[ActualStartTime] ),
MAX ( Table1[ActualFinishDate] ) + MAX ( Table1[ActualFinishTime] ),
SECOND
)
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
