CODEWITHSUNDEEP
powerbidax
Gabi
Gabi

Reputation: 37

Power BI Dax Calculate Time Intervals With Complicated Logic

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---

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!!!

Upvotes: 1

Views: 981

Answers (1)

Phil Leh
Phil Leh

Reputation: 758

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
        )
    )

Upvotes: 0

Related Questions