Get Previous Date and Status with Multiple Groupings in Single Table

[Anonymous]

I have a table with the following columns:

Note that there's a filter to only show one LOA_UID, but the table contains multiple LOA_UIDs.

I'm trying to get two additional columns:

1. The Previous Responsibility date (ie, in rows 1-3, I'd expect blank; in rows 4-5, I'd expect 11/7/2022 10:14:36 PM, and so on).

2. The Previous Responsibility (ie, in rows 1-3, I'd expect blank; in rows 4-5, I'd expect "ABC", and so on).

This is proving difficult because there's multiple levels of grouping.  Each Log_UID is unique across each LOA_UID, and as you can see, there's sorting by CreateDate. So I was unable to figure out the answer from a simple EARLIER command, since the table contains multiple LOA_UIDs.

Any help would be appreciated!

[Anonymous]

I should add: I was able to successfully add an index column grouped by LOA_UID and sorted by CreateDate.

But for some reason, I'm unable to do the same for Responsibility. I suspect having an index column for Responsibility would make things much easier.

For example, in the table below, rows 1-3 would have "1", rows 4-5 would have "2", rows 6-7 would have "3"

[v-xiaosun-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I made a sample and here is my solution.

Sample data:

Step1, Use Power Query to create a index column.

Step2, Create three columns.

RANK =
RANKX (
    FILTER (
        'Table',
        'Table'[responsibility] <> EARLIER ( 'Table'[responsibility] )
    ),
    'Table'[Index],
    ,
    ASC,
    DENSE
)

_responsibility =
CALCULATE (
    MAX ( 'Table'[responsibility] ),
    FILTER ( 'Table', 'Table'[RANK] < EARLIER ( 'Table'[RANK] ) )
)

_date =
CALCULATE (
    MAX ( 'Table'[createdate] ),
    FILTER (
        'Table',
        'Table'[RANK] < EARLIER ( 'Table'[RANK] )
            && 'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 2
    )
)

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

That does the trick. Major kudos - thank you so much!