Transaction analysis

[Phil-osophy]

Hey everyone,

i have a new dataset that i am trying to do some analysis on but cant seem to solve.

i have a file with 4 columns (employee name, transaction date, transaction amount, and transaction type.

what i am trying to do is come up with a measure that calculates the number of cash withdrawals per day for any employee (employee name will be used as a slicer).  

i am also trying to create a calculated column that goes through each employees transactions to see if a credit adjustment can be matched to any purchases (the amounts need to be identical). i was thinking of creating a calculated column but still not sure how to go about it in terms of the formula.

here is the link to my file:

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

Any help greatly appreciated

[TomMartens]

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

[Phil-osophy]

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.

[Phil-osophy]

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.

[Ronald123]

@Phil-osophy,

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;