Create new table that summarizes subsequent values based on ID

[Pjotr]

Hi everyone,

I am currently having a dataframe in the following format:

I would like to analyse the status transitions that occur and the time these transitions take.

For this I would like to obtain a dataset in the following format:

However, I am struggling to find a way to do this in DAX.

Does anyone have a suggestion on how this can be obtained?

Thanks in advance.

[javi0unavailabl]

Please, can you explain what do you mean with "status transitions" ? I don't know why the second table is constructed using the first.

Thanks.

[Pjotr]

Each ID takes on different status values over time.

For ID 1, the status values are 0 > 1 > 3 > 5.

I am interested in the transitions, so going from 0 > 1, 1 > 3, etc.

The value I would like to know for each transition is the difference in days between the two status values.

So for ID 1 and transition 0 > 1, this is 11-0 = 11 days.

For ID 1 and transition 1 > 3, this is 21-11 = 10 days.

[Zubair_Muhammad · Accepted solution]

@Pjotr

You can use this calculated table

From the Modelling Tab >> NEw Table

Calculated Table =
VAR temp =
    ADDCOLUMNS (
        Table1,
        "Status New",
        VAR nextrow =
            TOPN (
                1,
                FILTER ( Table1, [ID] = EARLIER ( [ID] ) && [Day] > EARLIER ( [Day] ) ),
                [Day], ASC
            )
        RETURN
            MINX ( nextrow, [Status] ),
        "Days Between",
        VAR nextrow =
            TOPN (
                1,
                FILTER ( Table1, [ID] = EARLIER ( [ID] ) && [Day] > EARLIER ( [Day] ) ),
                [Day], ASC
            )
        RETURN
            MINX ( nextrow, [Day] ) - [Day]
    )
RETURN
    SELECTCOLUMNS (
        FILTER ( temp, NOT ( ISBLANK ( [Status New] ) ) ),
        "ID", [ID],
        "Status Old", [Status],
        "Status New", [Status New],
        "Days Between", [Days Between]
    )

[Pjotr]

@Zubair_Muhammad Exactly what I needed, thanks a lot! 

[Zubair_Muhammad]

@Pjotr

See the File attached as well