Forum Discussion
Helper ITransaction Matching
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" )
Super UserHello Phil-osophy
The second match is not coming from the payment personal for Jared Vennett. The [
- 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"?
Helper IHi 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
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.