Previous transaction

Hey Daretoexplore ,

To create a "Previous Transaction" column based on CustID, you can use Power Query (M language) or DAX depending on where you're building this (e.g., Excel, Power BI, etc.). Below are both approaches.

Option 1: Power Query (M Language)

Power Query doesn’t use row numbers directly, but we can group and sort each customer’s transactions, then add an index to track the order.

Steps:

1. Load your data into Power Query.

2. Sort by CustID and a temporary index (if no date exists).

3. Group by CustID.

4. Inside each group:

   • Sort by the temporary index.

   • Add an Index column starting at 0.

   • Add another column that’s the Transaction Amount shifted by 1 row.

5. Expand the tables and rename the new column as "Previous".

M Code Sample:

let
    Source = YourTable,
    Sorted = Table.Sort(Source, {"CustID", Order.Ascending}),
    Grouped = Table.Group(Sorted, {"CustID"}, {
        {"AllData", each 
            let
                AddedIndex = Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type),
                Shifted = Table.AddColumn(AddedIndex, "Previous", each try AddedIndex[Transaction Amount]{[Index]-1} otherwise null)
            in
                Shifted
        , type table}
    }),
    Expanded = Table.Combine(Grouped[AllData])
in
    Expanded

Option 2: DAX (Power BI)

If you're using DAX in a calculated column:

PreviousTransaction = 
VAR CurrentCust = 'Table'[CustID]
VAR CurrentIndex = 'Table'[Index] -- Add an index if needed
RETURN
    CALCULATE(
        MAX('Table'[Transaction Amount]),
        FILTER(
            'Table',
            'Table'[CustID] = CurrentCust &&
            'Table'[Index] = CurrentIndex - 1
        )
    )

• If you don’t have an index or timestamp, create an index using Power Query before this.

Output Example (Expected)

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam