You’re absolutely right – I was WAY overcomplicating this. With a fresh look at it, all I needed to do to adapt the solution you provided was to create one more variable to calculate the first date for each action, and then wrap the RETURN expression in a CALCULATE ( IF ( ) ) to handle the first date issue and any subsequent blank dates.
Greatly appreciate your expertise and willingness to work through this with me. I’ve learned a ton through this process, and will be able to reuse this measure repeatedly across a lot of different models that deal with sequential process data.
Here’s the final measure:
DaysBetweenProcessSteps =
VAR FirstOverallDate =
CALCULATE (
MIN ( FactHrActionsStatus[ProcessStepCompletionDate] ),
ALLEXCEPT ( FactHrActionsStatus, FactHrActionsStatus[HrActionID] )
)
VAR IndexNum =
MAX ( FactHrActionsStatus[ProcessStepIndex] )
VAR PreviousIndexNum =
CALCULATE (
MAX ( FactHrActionsStatus[ProcessStepIndex] ),
FILTER (
ALLSELECTED ( FactHrActionsStatus ),
FactHrActionsStatus[ProcessStepIndex] < IndexNum
)
)
VAR CurrentDate =
VALUE ( ( SELECTEDVALUE ( FactHrActionsStatus[ProcessStepCompletionDate] ) ) )
VAR PriorDate =
VALUE (
(
CALCULATE (
SELECTEDVALUE ( FactHrActionsStatus[ProcessStepCompletionDate] ),
FILTER (
ALL ( FactHrActionsStatus ),
FactHrActionsStatus[ProcessStepIndex] = PreviousIndexNum
)
)
)
)
RETURN
CALCULATE (
IF (
MIN ( FactHrActionsStatus[ProcessStepCompletionDate] ) = FirstOverallDate,
1,
IF (
SELECTEDVALUE ( FactHrActionsStatus[ProcessStepCompletionDate] ) = BLANK (),
0,
CurrentDate - PriorDate
)
),
FactHrActionsStatus[HrActionID]
)
Thanks again!
Brian