cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## transaction matching

hey everyone!

i have a follow up to my last post in regards to transactions. this time my data set problem is focused on trying to match the credit adjustments that correspond to a specific traveller's purchases. the credit adjustments were due to errors in transactions for which travellers got refunded.

im currently only able to match a portion of them because i cant find a suitable formula that will parse through each record, read the employee id, and keep that traveller's previous transactions in context when trying to match the corresponding credit adjustment.

so far i created 3 additional columns in power bi:

CALCULATE(MAXX(Sheet1,Sheet1[amount]), Sheet1[Employee ID] = EARLIER(Sheet1[Employee ID]), FILTER(Sheet1, Sheet1[transaction type] = "credit adjustment"))

purchase column = IF(Sheet1[transaction type] = "purchase", Sheet1[amount],0)

credit matched with purchase = IF(Sheet1[credit adjustment column] *-1 = Sheet1[purchase column] && ISNUMBER(Sheet1[credit adjustment column]), "matched", "nope")

this is what i have so far however due to the limitations of the credit adjustment column formula, its only taking into consideration the MAX for each traveller but i would like it to keep into consideration every transaction for a given traveller when performing the calculation.

my expected output is that Jim halpert has one matched purchase/credit adjustment for -125/125, Margaret Carter has one matched for -1250/1250, Michael J Fox has one for -350/350, and Robert LeRoy Parker has one for -13/13

any tips to move forward greatly appreciated!
2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: transaction matching

Hi @Phil-osophy ,

To create two calculated columns as below.

```Column =
- IF ( Sheet1[transaction type] = "credit adjustment", [amount], BLANK () )
```
```Column 2 =
VAR a =
CALCULATETABLE (
VALUES ( Sheet1[Column] ),
FILTER ( ALL ( Sheet1 ), Sheet1[Employee ID] = EARLIER ( Sheet1[Employee ID] ) ),
Sheet1[Column]
)
RETURN
IF ( [amount] IN a, "matched", "nope" )
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Regular Visitor

## Re: transaction matching

@v-frfei-msft thank you for your help!

it seems to be working correctly!

2 REPLIES 2
Community Support Team

## Re: transaction matching

Hi @Phil-osophy ,

To create two calculated columns as below.

```Column =
- IF ( Sheet1[transaction type] = "credit adjustment", [amount], BLANK () )
```
```Column 2 =
VAR a =
CALCULATETABLE (
VALUES ( Sheet1[Column] ),
FILTER ( ALL ( Sheet1 ), Sheet1[Employee ID] = EARLIER ( Sheet1[Employee ID] ) ),
Sheet1[Column]
)
RETURN
IF ( [amount] IN a, "matched", "nope" )
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Regular Visitor

## Re: transaction matching

@v-frfei-msft thank you for your help!

it seems to be working correctly!

Announcements