Receivable Balance Measure

[Anonymous]

Hello -

Having trouble with arriving at the Receivable Balance calculation Measure. Goal is to provide the Balance of Customer as of a particular Historical date . 

I tried using the measure to calculate Invoice value - All Receipts, as of the sellected  date, but does not seem to be working. Not sure wht i am messing up. Please help. 

Invoice Table 

Customer	Invoice	Invoice Date	Value
CUS1	INV-L1	8/20/2023	100
CUS1	INV-L2	8/20/2023	150
CUS1	INV-1	9/1/2023	500
CUS1	INV-2	9/15/2023	1000
CUS1	INV-3	9/25/2023	2000
CUS5	INV-6	10/4/2023	1100

Receipts of all invoices and any advance payment 

Customer	Invoice	Rcpt Date	Value
CUS1	INV-L1	9/5/2023	-100
CUS1	INV-1	9/1/2023	-200
CUS1	INV-1	9/29/2023	-200
CUS1	INV-2	9/30/2023	-1000
CUS1	INV-1	10/2/2023	-50
CUS2	ADV-1	9/20/2023	-250

Created measure -  

Balance = CALCULATE(SUM(InvTable[Value]), FILTER(ALL(InvTable[Invoice Date]),InvTable[Invoice Date]<= MAX('Calendar'[Date]))) -

CALCULATE(SUM(RcptTable[Value]), FILTER(ALL(RcptTable[Rcpt Date]),RcptTable[Rcpt Date] <= MAX('Calendar'[Date])))

Current Month measure need to select the balnce for that month. ( all invoices happened in the month and less receipts in that month )

Expected results if selected date - 8/31/2023

Customer	Balance	Current Month
CUS1	250	250

Expected results for selected date 9/30/2023 

Customer	Balance	Current Month
CUS1	2250	2100
CUS2	-250

Pbix attached   https://drive.google.com/file/d/1k4aGruU1ObhUurwYHRocYP5QC0Tj2vfq/view?usp=sharing

[danextian · Accepted solution]

Hi @Anonymous ,

You can simply your calculation but using the related calendar table instead of the facts table in your measure so for balance, that would be

CALCULATE (
        SUM ( InvTable[Value] ) + SUM ( RcptTable[Value] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
    )

What I don't get is your current month number for CUS1. Per data, that should be 2000 and not 2100 (3500-1500).

Please see attached pbix for details.

[Anonymous]

Thanks @danextian .You are right. It is 2000. Good solution. 

[123abc]

It looks like you are trying to create a measure to calculate the receivable balance for a specific historical date. The measure you provided seems close, but it might need a few adjustments. Here's an updated version of your measure:

Receivable Balance =
CALCULATE(
SUM(Invoice[Value]),
FILTER(
ALL(Invoice),
Invoice[Invoice Date] <= MAX('Calendar'[Date])
)
) -
CALCULATE(
SUM(Receipts[Value]),
FILTER(
ALL(Receipts),
Receipts[Rcpt Date] <= MAX('Calendar'[Date])
)
)

Here, I've replaced InvTable and RcptTable with Invoice and Receipts to match the table names you provided. Also, I removed the unnecessary SUMX function and simplified the formula.

Make sure to replace Invoice and Receipts with the actual names of your tables.

Now, you can use this measure in your visualizations. If you want to get the receivable balance for a specific date, use the date as a filter context in your report or use it in a CALCULATE statement.

For the current month measure, you can use a similar approach:

Current Month Receivable Balance =
CALCULATE(
[Receivable Balance],
FILTER(
ALL('Calendar'),
'Calendar'[Date] >= STARTOFMONTH(MAX('Calendar'[Date])) &&
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)

This measure calculates the receivable balance for the current month based on the selected date.

Now, when you select a specific date, you should get the expected results for the receivable balance and the current month receivable balance in your report.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

Thanks @123abc for the help. 

I had trouble with receivable balance measure with the above approach.