Calculation between Rows with conditions

[snaseem]

I have below sample dataset where I am looking for compute outstaning balances.

Invoice is issued against certain services with due date to pay, and cash receipt is issued when we receive the cash against respective invoice(s). It does not has to be per invoice calculation, but overall outstanding balance is fine. There could be instances when we receive the full or partial/no payments.

trans_date	Trans_type	Invoice #	Due Date	Amount
4/24/2023	Invoice	101	5/24/2023	100
4/24/2023	Invoice	202	5/24/2023	200
4/24/2023	Invoice	301	5/24/2023	300
4/24/2023	Invoice	401	5/24/2023	400
4/24/2023	Invoice	501	5/24/2023	500
6/13/2023	Cash Receipt	101	7/13/2023	-100
6/13/2023	Cash Receipt	202	7/13/2023	-200
6/13/2023	Cash Receipt	301	7/13/2023	-300
6/13/2023	Cash Receipt	401	7/13/2023	-400
6/13/2023	Cash Receipt	501	7/13/2023	-400

Need help how to achieve this. 

[Anonymous]

Hi @snaseem 

You can refer to the following calculated column

Differ =
VAR sum_invoice =
    SUMX ( FILTER ( 'Table', [Trans_type] = "Invoice" ), [Amount] )
VAR sum_cash =
    SUMX ( FILTER ( 'Table', [Trans_type] = "Cash Receipt" ), [Amount] )
RETURN
    sum_invoice + sum_cash

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bmejia]

Can yo just create a matrix table and drag and drop the Invoice # to Rows, Trans Type to Columns and Amount to Values.  The total will give you the outstanding balance

 Updating Media

[snaseem]

Can we do this with the help of dax? I may need to add some other analysis as well such as avg time to pay the invoice etc.

[Bmejia]

yes, but if you want to actually type the dax formulas it be something like this 

Invoice = CALCULATE(SUM('Sample'[Amount]),'Sample'[Trans_type]="Invoice")

Cash Receipt = CALCULATE(SUM('Sample'[Amount]),'Sample'[Trans_type]="Cash Receipt")

Outstanding = [Invoice]-[Cash Receipt]

Average = CALCULATE(AVERAGE('Sample'[Amount]))

[snaseem]

@amitchandak @Ritaf1983 @Ahmedx