FirstSortedValue - KPI - First negative value in a table chart

[jerryr125]

Hi - I thought I would try again and re-post:

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.

Therefore, the KPI should display :  08/30/2020

thoughts ? 

thanks - Jerry

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

[brunobertels]

may be this 

firsortedvalue(Date, 

- if( 

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

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

) , Date)

[jerryr125]

getting closer - but still not working.

I changed 'Date' to SALECALENDARDATE

Perhaps a () off somewhere in the formula

thoughts ? Jerry

[sunny_talwar · Accepted solution]

May be this

FirstSortedValue(SALECALENDARDATE, -Aggr(
If($(vProductBOHFinal)-Aggr(Rangesum(Above(($(vProductForeCastTotal)), 0, RowNo())), SALECALENDARDATE) > 0, -1E10, $(vProductBOHFinal)-Aggr(Rangesum(Above(($(vProductForeCastTotal)), 0, RowNo())), SALECALENDARDATE)), SALECALENDARDATE))

or may be this to simplify it a little

FirstSortedValue(SALECALENDARDATE, -Aggr(
If($(vProductBOHFinal)-Rangesum(Above(($(vProductForeCastTotal)), 0, RowNo())) > 0, -1E10, $(vProductBOHFinal)-Rangesum(Above(($(vProductForeCastTotal)), 0, RowNo()))), SALECALENDARDATE))

[Kushal_Chawda]

@jerryr125  also try below

firstsortedvalue(SALECALENDARDATE, 

aggr(if( 

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

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

,SALECALENDARDATE) , SALECALENDARDATE))

[Kushal_Chawda]

@jerryr125  or try below

firstsortedvalue(SALECALENDARDATE, 

aggr(if( 

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

,SALECALENDARDATE) , 

SALECALENDARDATE))

[sunny_talwar]

@Kushal_Chawda this will sort based on SALECALENDARDATE, won't it?

[Kushal_Chawda]

@sunny_talwar  Yes. Basically first to calculated correct cumulative figures with sorting date  and then once identified negative from that just assign  dates and find earlier date.

[sunny_talwar]

@Kushal_Chawda  Oh because it is an accumulated value... that sounds promising and smart way to do it.

[jerryr125]

Hi everyone - thank you for your assistance !

The second one did the trick - appreciate it ! Jerry