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" )
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!
Super UserI 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.