Firstsorted Value

[P_Kumar]

Hi,

As per attached files and screenshots,  I  have Bonus data only for Apr, Jul and Oct. 

1. When a user select 'June' from filters, it should show the nearest first available bonus.. i.e. 'Apr' Bonus amount

2. When a user select 'Sep' from filter, it should show the nearest first available bonus.. i.e. 'Jul' Bonus amount

similarly,

3. When a user select 'Dec' from filter, it should show the nearest first available bonus.. i.e. 'Oct' Bonus amount

Please advise.

[sunny_talwar]

okay much clearer now. I think the expression I provided should have worked, but if it hasn't I would need to look at a sample where I can see it failing. Does that make sense?

[P_Kumar]

Yes, Sunny.  Prior to that I have been trying to put the formulae you have given into a KPI but not working.  Attached qvf.

[sunny_talwar · Accepted solution]

Try this for the KPI

Sum({<[Month Number] = {"$(=Max({<scenario = {[*]}, Month, [Month Number] = {[<=$(=Max([Month Number]))]}>} [Month Number]))"}, Month>} Amount)

[P_Kumar]

Thanks a lot Sunny.. working fine for KPI and I have tweaked it for my scenario too.. Thanks again!!

[sunny_talwar]

Awesome!!