Power BI Dax Calculate Time Intervals With Complicated Logic

user_17624673 0
Viewed 353

I want to calculate the lag time of each task in this table. A lag calculates the time interval between a task is completed and the next task is initiated.

There are cases (CaseNum), a case can have different tasks (TaskNum), a stage (TaskStage) represents the current stage of a task (but it doesn't need to be continuous, we can skip a particular stage to the next one).

Table

rank is a reference column I created to show the order of task stages under each case.

The logic of Lag is quite complicated---

  • For S1: InitDate - ApproveDate
  • For S2: InitDate - CompletedDate of the last completed task at S1 stage. (If there isn't any tasks at S1 stage, it will be InitDate - ApproveDate)
  • For S3: InitDate - CompletedDate of the last completed task at S2. (If there isn't any tasks at S2 stage, it will be InitDate - CompletedDate of the last completed task at S1, If there isn't any tasks at S1 stage, it will be InitDate - ApproveDate)
  • For S4: InitDate - CompletedDate of the last completed task at S3. (same logic: If there isn't any tasks at S3 stage, it will be InitDate -
    CompletedDate of the last completed task at S2, If there isn't any tasks at S2 stage, it will be InitDate - CompletedDate of the last completed task at S1, If there isn't any tasks at S1 stage, it will be InitDate - ApproveDate)
  • If the lag is negative, keep it as zero.

The background color in the table indicates pairs of dates I use to calculate the lag. Hope it can help with understand the logic. THX!!!

dax powerbi
1 Answers

You could try the following, assuming you try to calculate a calculated column:

Lag = 
-- getting a table with lagged rows (ranks smaller then current rows rank)
VAR lags =
    FILTER (
        ALL ( 'Table' ),
        'Table'[CaseNum]
            -- EARLIER() will get the 'outer' row context's CaseNum
            -- outer row context: calculated column one
            = EARLIER ( 'Table'[CaseNum] )
            && 'Table'[rank]
                < EARLIER ( 'Table'[rank] )
    )

-- extracting only the direct previous rank
VAR lag_value =
    MAXX (
        lags,
        'Table'[rank]
    )

-- checking if there is a previous task (rank value is not blank)
VAR cond =
    ISBLANK ( lag_value )

-- getting currents row InitDate (in calculated columns row context)
VAR current_date = 'Table'[InitDate]

-- getting the date from the previous task 
VAR last_date =
    CALCULATE (
        MAX ( 'Table'[CompletedDate] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[CaseNum]
                = EARLIER ( 'Table'[CaseNum] )
                && 'Table'[rank] = lag_value
        )
    )
RETURN
    IF (
        cond,
        -- if blank, return the days between approval and init
        DATEDIFF (
            'Table'[ApproveDate],
            'Table'[InitDate],
            DAY
        ),
        -- else return days between last completed and init, 
        -- negative values will be set to 0
        MAX (
            DATEDIFF (
                last_date,
                current_date,
                DAY
            ),
            0
        )
    )
user_139631930
Related