topic Help needed: DAX for opening and closing balance for invoices in DAX Commands and Tips

Help needed: DAX for opening and closing balance for invoices

Rakshana — Thu, 05 Oct 2023 11:10:40 GMT

Hi all
I have data where payments are made against invoices. Every invoice can have one or many payment vouchers.
I have written 2 measures

OpenBal = CALCULATE([Billings] - [Collections],
FILTER(ALL(MasterDate[Date]),MasterDate[Date]<MIN(MasterDate[Date])))

ClosingBal =
var closebal = CALCULATE([Billings] - [Collections] ,
FILTER(ALL(MasterDate[Date]),MasterDate[Date]<=MAX([Date])))

return IF([Collections]<0,0, closebal) --Sometimes there are debit payments so the negative collections should mean there's no balance for the customer to pay

But when brought in a matrix, the measure calculates line wise and the result shows like this -

	March
	OpenBal	Billings	Collections	ClosingBal
Invoice1		30,000	30,000	0
Voucher1		30,000	18,000	12,000
Voucher2		30,000	12,000	18,000

Both Voucher1 and Voucher2 are marked against the same invoice.
Even though total shows 0 here, the individual balances are carried over in the next few months. How can I rectify this.
Any advice/suggestion highly appreciated.
tamerj1 Greg_Deckler

Re: Help needed: DAX for opening and closing balance for invoices

BeaBF — Thu, 05 Oct 2023 18:48:51 GMT

Rakshana Hi! You can use SUMX and FILTER to iterate through each invoice and its associated vouchers to calculate the open balance correctly:

OpenBal =
SUMX (
FILTER (
ALL ( 'YourTableName' ),
'YourTableName'[InvoiceID] = EARLIER ( 'YourTableName'[InvoiceID] )
&& 'YourTableName'[VoucherDate] < EARLIER ( 'YourTableName'[VoucherDate] )
),
'YourTableName'[Billings] - 'YourTableName'[Collections]
)

ClosingBal =
SUMX (
FILTER (
ALL ( 'YourTableName' ),
'YourTableName'[InvoiceID] = EARLIER ( 'YourTableName'[InvoiceID] )
&& 'YourTableName'[VoucherDate] <= MAX ( 'YourTableName'[VoucherDate] )
),
IF ( 'YourTableName'[Collections] < 0, 0, 'YourTableName'[Billings] - 'YourTableName'[Collections] )
)

Try, BBF

Re: Help needed: DAX for opening and closing balance for invoices

Rakshana — Fri, 06 Oct 2023 10:08:43 GMT

Hey thank you for your response.
But this throws a syntax error for EARLIER
Is there any other way to calculate Opening and Closing balance without it forming a circular dependency.