Hi Experts,
I am struggling to get the formula below. Attached is the data set. Please help me.
Attaching the .pbix file.
Invoiced Amount = Total Amount Based on VHDUN Number for the current Date and remaining date it should display Closing (As per the below screen shot
Second thing I need to highlight the dates where ever it amount is crossing the line.
Please suggest and let me know if you need any further more details.
Any help greatly appreciated.Text.pbix (1.2 MB)
Will this work for your visual?
Added to the visual as lines - adjusting the “Shapes” setting in the Format area and toggled on the ‘customize series’ option. 
For Closing and Limit, I reduced the line size (to 1pt instead of 3pt), and for Closing Above and Closing Below I turned OFF the lines, and turned ON the markers)
Text.pbix (1.2 MB)
Thanks for your suggestion. They specifically needed highlight the dates either background color as gray in Column
Any help much appreciated.
well, I don’t think there is a way to conditionally format the axis colors with default visuals. But perhaps this provides a good enough work around?
I modified the Closing Below measure, and moved it to the columns section of the visual
Closing Below =
VAR Spacing = [Payments Due] + [Deliveries]RETURN
IF( [Closing] < [Limit], [Limit] - Spacing, BLANK() )
Text.pbix (1.2 MB)
Thanks this helped me a lot.
Also could you please help me with the table issue? I am unable to bring the Closing Value as Next Day Invoice Amount
Some one please help on this quickly… sorry for the continuous follow up.
I have to admit, I’m stumped at the moment, but I will take a look at this later when I take my next break.
The issue you have right now is that the Closing Amount is dependent on the Invoiced Amount, so when you try to bring it into the next day (even as the value from the prior date), you are creating a circular dependancy.
Instead, we need to come up with a way to calculate Closing that is NOT dependent on Invoiced Amount (even bringing in the Closing amount from another measure does not work)
Thanks, Heather. I appreciate your time and help.
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)
Hi @putturps, we’ve noticed that no response has been received from you since the 10th of November. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!