Forum Discussion
Receivable Balance Measure
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 -
| 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
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.
4 Replies
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.
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.
Thanks danextian .You are right. It is 2000. Good solution.
