Transaction Matching

[Phil-osophy]

Hey everyone,

this post is follow up to my previous one as i have found some errors in the output.

Credit Adjustment Column =
- IF ( Sheet1[transaction type] = "credit adjustment", [amount], BLANK () )

Credit adjustment match with equivalent purchase =
VAR ABC =
    CALCULATETABLE (
        VALUES ( Sheet1[Column] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Employee ID] = EARLIER ( Sheet1[Employee ID] ) ),
        Sheet1[Column]
    )
RETURN
    IF ( [amount] IN ABC, "matched", "nope" )

In short, the credit adjustment formula is working correctly but with some exceptions. The credit adjustment column that @v-frfei-msft proposed is working correctly at identifying the dollar amounts for credit adjustments. The issue is that the credit adjustment variable that was created isnt taking into account wether the matching transaction is a credit adjustment, its only taking into consideration whether the dollar values match. I updated my data set to include one of these exceptions.

here is the excel dataset:

https://drive.google.com/file/d/1_7ajZyhZ1Vg7UN73VxDmFpqILld01Adk/view?usp=sharing 

The issue is with Jared Vennett.  The credit adjusment column from  @v-frfei-msft solution is correctly picking up that Jared has a credit adjustment of $(63.00) which is the final transaction in the list. The issue is that the credit adjustment variable is matching that in addition to the "Payment - Personal" of $(63.00) with the 2 purchases of 63.00.

My desired output is for it to only match with the credit adjustment  and not the "Payment - Personal".

Any help on this is greatly appreciated!

Thanks

[jdbuchanan71]

Hello @Phil-osophy 

The second match is not coming from the payment personal for Jared Vennett.  The [

Credit adjustment match with equivalent purchase] calcuation does the following.

1. Build a table of all the [Credit Adjustment Column] amounts where the Employee ID is the same as the row we are on.
2. Check to see if the amount for the row we are on is in that list.

For Jarred Vennet lines in that table looks like this.

Employee ID	Credit Adjustment Column
A561634	63.00

 And that is coming from just this one line

Now it compares the [Amount] to that list and looks for a match in the [Credit Adjustment Column].  Because Jared Vennett has two purchases for 63 they both match the amount in the [Credit Adjustment Column].

If you are saying only one of the "Purchase" lines should be tagged as a match, how would you determine which one?  If you were filling out the matched column manually, what logic would you use to decide which of the two purchace lines to mark as "Matched"?

[Phil-osophy]

Hi @jdbuchanan71 

At the moment im more concerned about aggregate amounts being netted out. That being said, as long the first one (or any one as long as the number of credit adjustments that are matched is equal to the number of purchases unlike in this situation) that appears is removed, the ananlysis would still be slightly imperfect but acceptable for my needs.

Is there any way to do this that you would know of?

Thanks!

[jdbuchanan71 · Accepted solution]

@Phil-osophy 

I think this will do the trick for you.  It allows the match to only apply to the first purchase date that matches the amount.

Credit adjustment match with equivalent purchase = 
VAR ABC =
    CALCULATETABLE (
        VALUES ( Sheet1[Credit Adjustment Column] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Employee ID] = EARLIER ( Sheet1[Employee ID] ) ),
        Sheet1[Credit Adjustment Column]
    )
VAR MatchDate = 
    CALCULATE(
        MIN(Sheet1[transaction date]),
        FILTER ( ALL(Sheet1), 
        Sheet1[Employee ID] = EARLIER ( Sheet1[Employee ID] ) &&
        Sheet1[transaction type] = "purchase" &&
        Sheet1[amount] = EARLIER(Sheet1[amount])
        ))

RETURN
    IF ( [amount] IN ABC && [transaction date] = MatchDate, "matched", "nope" )

[Phil-osophy]

Hi @jdbuchanan71,

Your solution seems to be working for some but not for all. I added updated my dataset an added an exception to it. The issue i came across is that Quentin has 5 purchases and 5 credit adjustments all for the same amounts, however it is only matching one credit adjustment with one purchase. The others aren't being picked up. 

I believe the issue is that all 5 credit adjustments were for $20, therwise your solution works well. Any idea how to fix this?

here is the link to the updated dataset: https://drive.google.com/file/d/1VFNHhLZG6ESQtXYU4jgA6HjSpeOfkIYk/view?usp=sharing

Thanks!

[jdbuchanan71]

Hello @Phil-osophy 

Based on the sample data set I can't think of a way to handle that problem.  There is nothing the can be used to uniquely identify a row for any sort of ranking to identify $20 credit 1 vs $20 credit 4 and align them with the purchases.