CALCULATE measure for current month

Question

Trying to calculate only the selected month outstanding invoices from an Invoice &amp; Receipts table and the measure is not working as expected.&nbsp;&nbsp;Selected date can be any date in previous months. If the selection is for the end of month of SEP 2023,&nbsp;1. Current Month total need to consider all the invoices for SEP and receipts for the invoices for SEP only. Receipts for prior month invoices should not be considered.&nbsp;&nbsp;&nbsp;Current Month = CALCULATE (
        SUM ( InvTable[Value] ) + SUM ( RcptTable[Value] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] &lt;= MAX ( 'Calendar'[Date] ) )
    )&nbsp;&nbsp;Balance for Customer should consider all invoices and receipts until the selected date&nbsp;2. Balance =CALCULATE (&nbsp; &nbsp; SUM ( InvTable[Value] ) + SUM ( RcptTable[Value] ),&nbsp; &nbsp; FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] &lt;= MAX ( 'Calendar'[Date] ) ))&nbsp;InvTable has all invoices and RcptTable contains receipts and any advance payments&nbsp;&nbsp;Invoice Table&nbsp;CustomerInvoiceInvoice DateValueCUS1INV-L18/20/2023100CUS1INV-L28/20/2023150CUS1INV-19/1/2023500CUS1INV-29/15/20231000CUS1INV-39/25/20232000CUS5INV-610/4/20231100&nbsp;&nbsp;Receipts of all invoices and any advance payment&nbsp;CustomerInvoiceRcpt DateValueCUS1INV-L19/5/2023-100CUS1INV-19/1/2023-200CUS1INV-19/29/2023-200CUS1INV-29/30/2023-1000CUS1INV-110/2/2023-50CUS2ADV-19/20/2023-250Expected result&nbsp; for selected date of 09/30/2023 ( End of Month)&nbsp;&nbsp;&nbsp;CustomerBalanceCurrent Month&nbsp;CUS122502000CUS2-250&nbsp;&nbsp;PBIX attached.&nbsp; Please advice.&nbsp; &nbsp;https://drive.google.com/file/d/1CRXVBsOICBmQHhcg5q9WXRbeA3roKXiK/view?usp=sharing&nbsp;

ashish_mathur · Answer

Hi,
Your answer of Balance seems wrong.&nbsp; PBI file attached.
Hope this helps.

&nbsp;
&nbsp;

anonymous · Answer

Ashish_Mathur&nbsp;Sorry. That is not adding up for Current month total. That is the issue that i posted.&nbsp;For Sep ,&nbsp; there are 3 invoices that adds up to 3500 (500 + 1000 + 2000)&nbsp; and receipts for SEP invoices adds up to&nbsp; 1400 (200 + 200 + 1000 ) . Current month total should show as 2100 and not 2250.&nbsp;

ashish_mathur · Answer

Nowhere in your original post have you shared that the answer should be 2,100.&nbsp; Anyways, the answer should not even be that - it should be 2,000 i.e. 3,500 - 1,500.&nbsp; The revised measure for this will be:
Current month total =&nbsp;SUM(Invoices[Value])+[Receipts]

anonymous · Answer

Sorry&nbsp;Ashish_Mathur&nbsp;.&nbsp; I think i didn't make it clear in my earlier post.&nbsp;Current Balance is actually the invoices that is balance for the month selected. So if there is a receipt for an invoice from previous month that should NOT be in the current month balance.&nbsp;In the example i shared, only INV-1 &amp; INV-2 are the invoices paid to be accounted for receipts.&nbsp; That's why receipts is 1400.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

ashish_mathur · Answer

I just cannot understand your question.&nbsp; Someone who does will help you.

Forum Discussion

CALCULATE measure for current month

5 Replies

Related content

DAX Calculated Measure

DAX Measure for Ranking the valid rows and calculating the average rating

Measure or Calculated Column

Measure to calculate

Measure/Model calculation support needed

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

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