How to obtain the previous step/transaction by id in Power BI and total time spent in rejection status?

Question

I have data in the format below where each transaction per ID is captured. I am trying to get a count of the transactions an ID was in prior to being rejected.

So for XYZ it would be: Acknowledge Receipt - 1 Pending Approval - 1

And overall the counts would look like: Acknowledge Receipt - 1 Pending Approval - 3 Approve Request - 1

I've tried adding a rank to offset the data but it's made complicated by ID's that have been rejected multiple times. Then adding on to that, I also wanted to see how long something sat in a rejection status prior to moving to the next step, or if it's still in rejection status. But I wanted to focus on one problem first. I'm a little stumped by how to solve this.

Answer 1

Create these 4 columns-

Column-1:

PREVIOUS_TRANSACTION_TYPE = 

var this_row_id = 'Table'[ID]
var this_row_tran_type = 'Table'[TRANSACTION_TYPE]
var this_row_tran_date = 'Table'[TRANSACTION_DATE]

var prev_max_date_same_id =
CALCULATE(
    max('Table'[TRANSACTION_DATE]),
    FILTER(
        all('Table'),
        'Table'[ID] = this_row_id
            && 'Table'[TRANSACTION_DATE] < this_row_tran_date
    )
)

var prev_max_date_tran_type =
CALCULATE(
    max('Table'[TRANSACTION_TYPE]),
    FILTER(
        all('Table'),
        'Table'[ID] = this_row_id
            && 'Table'[TRANSACTION_DATE] = prev_max_date_same_id
    )
)

RETURN prev_max_date_tran_type

Column-2:

ACKNOWLEDGEMENT_RECEIPT_COUNT = 
IF(
    'Table'[TRANSACTION_TYPE] = "Reject Request" 
        && 'Table'[PREVIOUS_TRANSACTION_TYPE] = "Acknowledgement Receipt",
        1
)

Column-3:

PENDING_APPROVAL_COUNT = 
IF(
    'Table'[TRANSACTION_TYPE] = "Reject Request" 
        && 'Table'[PREVIOUS_TRANSACTION_TYPE] = "Pending Approval",
        1
)

Column-4:

APPROVE_REQUEST_COUNT = 
IF(
    'Table'[TRANSACTION_TYPE] = "Reject Request" 
        && 'Table'[PREVIOUS_TRANSACTION_TYPE] = "Approve Request",
        1
)

Here is the output-