DAX formula help please

Assuming that I understood your requirement correctly (that Invoiced Amount is calculating properly with the exception of adding in the prior Closing amount), I think this is what you need:

New Invoiced Amount =
VAR InvoicedAR = CALCULATE( [OIL Total USD], ALL( ‘ Date ’ [Date] ) ) 
VAR InvoicedAR1 = InvoicedAR + [Deliveries] - [Payments Due] 
VAR MinDate = MIN( ‘ Date ’ [Date] ) 
VAR InvoiceARPreDay = CALCULATE( InvoicedAR1, PREVIOUSMONTH( ‘ Date ’ [Date] ) ) 
VAR _Table = ADDCOLUMNS( ‘ Date ’, “ @Amt ”, [Prior Closing Amt] ) 

RETURN
IF(
    MinDate = TODAY(),
    InvoicedAR,
    InvoiceARPreDay
) + SUMX( _Table, [@Amt] )

as I mentioned before, the issue was that you were trying to reference Closing (which is calculated using the Invoice Measure and others) in the Invoice Measure - this created a loop in the logic.

To break that loop, I am now calculating the prior day’s Closing amount inside of a virtual table.

Many thanks to @BrianJ, who pointed me to some previous solutions that had been presented in the forum. Thanks also to @Melissa, as her virtual tables in this particular entry helped greatly: Recursive Calculation - #4 by Melissa

Text.pbix (1.2 MB)

2 Likes