Forum Discussion
Transaction analysis
Hey,
based on your sample data, I used this DAX to count the withdrawals:
No of Cash Withdrawal =
CALCULATE(
COUNT(Sheet1[amount])
, KEEPFILTERS('Sheet1'[transaction type] = "Cash Withdrawal")
)
this allows to create this table visual:
Hope fully this is what you are looking for.
I have to admit that I have my difficulties to understand the requirements for discovering a match between "credit adjustment" and "purchase" besides that the amounts have to match. Can you please elaborate a little more about this, e.g. only look inside transactions of the same employee, and the duration may not exceed 12 days or somethig similar, if these kind of rules exist.
Regards,
Tom
Hey TomMartens !
thanks for the reply. soo i implemented the formula for the cash advances and it seems to be working.
concerning the credit adjustments, the idea is that if a person makes a purchase that they didnt intend, the amount will be refunded through a credit adjustment which is why the credit adjustment amounts and purchase amounts would need to match for the same individual for the analysis to work.
hey TomMartens ,
just to specify, the credit adjustments that can be matched to the purchase will be for the same amount but will be negative. from the dataset, the only valid credit adjustment would be for margaret carter because purchase amount and credit adjustment amount are equal (but opposite signs). The credit adjustment for Jim Halpert would not be valid (no matching purchase that it can be associated with).
Also, is there any way to adjust the cash withdrawal formula for cases where the amounts withdrawn on the same day are different? I spotted a few instances in another dataset where the formula didn't pick up the same day withdrawals because the $ amounts were different.
First make the column amount positve.
Amount2 = IF(CashWithdrawal[amount]<0;CashWithdrawal[amount]*-1;CashWithdrawal[amount])
Then search in the earlier transactions.
CreditAdjustment = CALCULATE( MAX(CashWithdrawal[amount]); FILTER( CashWithdrawal; [Employee name]=EARLIER([Employee name]) && CashWithdrawal[Amount2]=EARLIER(CashWithdrawal[amount]) && CashWithdrawal[transaction type]="credit adjustment" ))Result;
