Forum Discussion
Transaction Matching
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
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" )
5 Replies
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.- Build a table of all the [Credit Adjustment Column] amounts where the Employee ID is the same as the row we are on.
- 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"?
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!
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" )
