Display Date when Balance-on-Hand is less than zero (first occurance)

[jerryr125]

Hi - 

I have the following formula in a KPI :

=MIN(($(vBOHProductFinal))-aggr(Rangesum(Above(($(vForeCastProductTotal)),0,RowNo())),PSALECALENDARDATE))

This displays the FINAL negative value of the BOH Products over the dates (one year of dates)

I would like to display the actual date when the BOH Product goes from positive to negative.

Thoughts ? 

Thanks - Jerry

[sunny_talwar]

May be this, might need to change a little bit, but the idea is to use First Sorted Value function

FirstSortedValue(PSALECALENDARDATE, Aggr(($(vBOHProductFinal))-aggr(Rangesum(Above(($(vForeCastProductTotal)),0,RowNo())),PSALECALENDARDATE), PSALECALENDARDATE))

[jerryr125]

HI - unfortunately that did not work 😞 any other thoughts ? 

[jerryr125]

Hi - any other thoughts ?
- Jerry

[jerryr125]

HI - I have a couple tried a couple of variations of the above but no luck 😞

any thoughts ??? Thanks so much - Jerry

[Kushal_Chawda]

Please share a sample data with expected output 

[jerryr125]

Hi - thank you.

Here is an example of my data  below.

The data is in a table chart.  

The Forecast Balance on Hand is a master measure

 ($(vProductBOHFinal))-aggr(Rangesum(Above(($(vProductForeCastTotal)),0,RowNo())),SALECALENDARDATE)

so I tried this :

=MIN(($(vProductBOHFinal))-aggr(Rangesum(Above(($(vProductForeCastTotal)),0,RowNo())),SALECALENDARDATE)) and it returns the last value of the table chart below:

-4600

I want to actually return the first date in which the forecast balance is negative - hence 08/30/2020.

thoughts ? 

Date	Forecast Daily usage	Cumulative Usage	Forecast Balance on Hand
8/27/2020	3,100		10,000
8/28/2020	4,000	7,100	2,900
8/29/2020	1,000	8,100	1,900
8/30/2020	2,200	10,300	-300
8/31/2020	2,500	12,800	-2,800
9/1/2020	1,800	14,600	-4,600

[jerryr125]

Hi any thoughts ? is this possible ? Jerry

[jerryr125]

Hi - I am still a little puzzled on this formula.  Perhaps a 'FirstSortedValue' and if statement - any thoughts ? 

[jerryr125]

Hi - 

I tried something like this - no luck :

=IF(Aggr(($(vBOHProductFinal))-aggr(Rangesum(Above(($(vForeCastProductTotal)),0,RowNo())) < 0,FirstSortedValue(PALENDARDATE, Aggr(($(vBOHProductFinal))-aggr(Rangesum(Above(($(vForeCastProductTotal)),0,RowNo())),PCALENDARDATE), PCALENDARDATE)))

thoughts ?