PowerBI Get Previous row value according to filters

Question

I have a table with different objects and the objects evolve over time. One object is identified by object_number and we can track it with the object_line_number. And every evolution of the object has a status.

I want to calculate the time elapsed between some status.

Below is my table for one object_number "us1":

In yellow are the rowscontaining the starting date. They are found if (status_id = 0 and (old_status <> 0 or object_line_number = 1) and emergency_level = 1).

In green are the rows containing the ending date. They are found if (status_id =2,3,4,5 and old_status = 0).

The column old_status does not exist in the table. This is the status of the previous row (according to the object)line_number). I am retrieving it thanks to the following measure:

old_status = CALCULATE (
 MAX(fact_object[status_id]),
 FILTER (
        ALL(fact_object), 
        fact_object[object_line_number] = IF(fact_object[object_line_number]=1, fact_object[object_line_number], MAX (fact_object[object_line_number])-1)),
 VALUES (fact_object[object_number]))

I am in DirectQuery mode, so a lot of functions are not present for Calculated Columns, that's why I am using Measures.

Once that is done, I want then to be able to get for every green row the date_modification of the previous yellow row.

In this example, the result would be 4/4 then 1. So that I can calculate the time difference between the date_modification of the current green row and the date_modification of the previous yellow row.

So I was thinking of adding a new column named date_received, which is the date_modification of the previous yellow row;

From there, I just have to keep only the green rows and calculate the difference between date_modification and date_received.

My final calcul is actually to have this :

Result = (number of green rows which date difference between date_modification and date_received <= 15 min) / (number of green rows which DAY(date_modification) = DAY(date_received))

But I don't know how to do it. I have tried in the same spirit of the old_status measure to do this:

date_received = CALCULATE (
 MAX(fact_object[date_modification]),
 FILTER (
     ALL(fact_object), 
     (fact_object[object_line_number] =  MAX (fact_object[object_line_number])-1) &&  MY OTHER FILTERS
 ),
 VALUES (fact_object[object_number])

)

But didn't succeed.

In SQL, the equivalent would be like this:

SELECT 
    SUM(CASE WHEN (DATEDIFF(MINUTE, T.date_received, T.date_planification) <= 15) THEN 1 ELSE 0 END)  /
    SUM(CASE WHEN (DAY(T.date_received) = DAY(T.date_planification)) THEN 1 ELSE 0 END) as result
FROM (
    SELECT *, T.status_id as current_status,
        LAG(T.date_modification) OVER(PARTITION BY T.object_number ORDER BY T.object_line_number) as date_received,
        T.date_modification as date_planification
    FROM 
    (
        select *,
            LAG (status_id) OVER(PARTITION BY object_number ORDER BY object_line_number) AS old_status
        from dbo.fact_object
    ) AS T
    WHERE ((T.status_id = 0 AND (T.old_status <> 0 OR T.object_line_number = 1) AND T.emergency_level = 1) OR (T.old_status = 0 AND T.status_id IN (2,3,4,5)))--974
) AS T
WHERE old_status = 0

(Well maybe there is a better way to do it in SQL that I've done).

How can I achieve this?

Katrina Katrina · Accepted Answer · 2017-01-17T07:20:11

In this case, I would first sort the table by Object then by Object Line number (or Date Modified - but these appear not to change for each row.)

Duplicate the table

Table1 - Add an index starting at 1 Table2 - Add an index starting at 0

From Table1, merge it with table2, using the new index fields

Expand the MergedStatus ID (rename to NextStatusID) and the Object (rename to NextObject) field.

You can now create a new conditional column to compare the two status fields. E.g. Status 2 = If Object = NextObject then [NextStatusID] else null

PowerBI Get Previous row value according to filters

1 Answers