I need help to create a DAX FORMULA to reconcile the POS credit card transaction amount x bank deposit. When the credit card deposit is the same of POS amount is easy (match). But sometimes the credit card deposit in the bank more than one transaction in the same batch.
The Amex Table below has some examples …
Example 1
POS transactions: 1/5/2017 $55.00 1/6/2017 $64.00 (outstanding transactions)
bank deposit: 1/8/2017 $119.00 (unrecorded bank transaction)
Example 2
POS transactions: 1/10/2017 $24.50 1/11/2017 $150.00 1/12/2017 $26.50 (outstanding transactions)
bank deposit: 1/13/2017 $201.00 (unrecorded bank transaction)
Thanks
| Amex Table | | | | | | | |
| ID | DESCRIPTION | POS DATE | BANK DATE | POS AMOUNT | BANK AMOUNT | DIFFERENCE | Comment |
| Amex - 890.00 | Amex Deposit | 1/2/2017 | 1/5/2017 | $890.00 | $890.00 | $0.00 | match |
| Amex - 100.00 | Amex Deposit | 1/4/2017 | 1/7/2017 | $100.00 | $100.00 | $0.00 | match |
| Amex - 55.00 | Amex Deposit | 1/5/2017 | | $55.00 | | $55.00 | outstanding transaction |
| Amex - 64.00 | Amex Deposit | 1/6/2017 | | $64.00 | | $64.00 | outstanding transaction |
| Amex - 64.00 | Amex Deposit | 1/9/2017 | 1/12/2017 | $64.00 | $64.00 | $0.00 | match |
| Amex - 24.50 | Amex Deposit | 1/10/2017 | | $24.50 | | $24.50 | outstanding transaction |
| Amex - 150.00 | Amex Deposit | 1/11/2017 | | $150.00 | | $150.00 | outstanding transaction |
| Amex - 26.50 | Amex Deposit | 1/12/2017 | | $26.50 | | $26.50 | outstanding transaction |
| Amex -119.00 | Amex Deposit | | 1/8/2017 | | $119.00 | -$119.00 | unrecorded bank transaction |
| Amex - 201.00 | Amex Deposit | | 1/13/2017 | | $201.00 | -$201.00 | unrecorded bank transaction |
