Previous transaction

[Daretoexplore]

Hey

So I have data that looks like this

CustID.     Transaction Amount

123WDX.   $345

456FGT.      $576

123WDX.   $200

I'm trying to add a column which adds the value of the previous transaction on the same row as the last, without any date parameters.

So in this instance, I'd like

CustID.     Transaction Amount  Previous

123WDX.   $345.                        Unknown

456FGT.      $576.                        Unknown

123WDX.   $200.                         $345

Appreciate it's a tricky one but would love to see if anyone has any views. Many thanks

[v-tejrama]

Hi @Daretoexplore,

Just wanted to check if you had the opportunity to review the suggestion provided by @Nasif_Azam.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Tejaswi.

[v-tejrama]

Hi @Daretoexplore, 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you.

Tejaswi.

[v-tejrama]

Hi @Daretoexplore,

I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.

If your question has been answered, please “Accept as Solution” and Give “Kudos” so others with similar issues can easily find the resolution.

Thank you.

Tejaswi.

[Nasif_Azam · Accepted solution]

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

[m4ni]

Hi,

You may want to try using the OFFSET function in DAX which allows you to move up or down in data.

Link to details:   OFFSET function (DAX) - DAX | Microsoft Learn