Forum Discussion

Phil-osophy's avatar
Phil-osophy
Helper I
7 years ago

Transaction analysis

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 trans...
Need Help
Phil-osophy's avatar
Phil-osophy
Helper I
7 years ago

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's avatar
Ronald123
Resolver III
7 years ago

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;