How to sequence a transaction by customer in Power Pivot?

Question

I need help regarding a Power Pivot formula which should have the output in the sequence column.

This is a snippet of Transaction history table containing the transaction ID, timestamp, and the customer details.

transaction_id	timestamp	customer	sequence
0001	2022-01-01 08:00:00	Customer A	1
0002	2022-02-01 08:01:23	Customer A	2
0003	2022-03-01 08:02:45	Customer A	3
0004	2022-01-01 08:00:00	Customer B	1
0005	2022-02-01 08:01:23	Customer B	2
0006	2022-01-01 08:02:45	Customer C	1

I'm trying to add a column to generate the sequence of the transaction (based on the timestamp) per customer.

So far, I've tried using the RANK.EQ function and wrapping it in the CALCULATE function then applying a FILTER; and I suspect that this is where it breaks down.

=CALCULATE(RANK.EQ([timestamp], transaction_history[timestamp], ASC], 
           FILTER(transaction_history, transaction_history[customer]=[customer]))

What am I doing wrong?

Answer 1

Personally I'd do it like this:

=
VAR IDs = transaction_history[transaction_id]
VAR Customers = transaction_history[customer]
RETURN
    CALCULATE(
        COUNTROWS(
            FILTER(
                ALL( transaction_history ),
                transaction_history[customer] = Customers
                    && transaction_history[transaction_id] <= IDs
            )
        )
    )