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 · Accepted solution]

If this is viewed only based on selection, you can try this

FirstSortedValue({<[Month Number] = {"$(='<=' & Max([Month Number]))"}, Month>} Aggr(Sum({<[Month Number] = {"$(='<=' & Max([Month Number]))"}, Month>} Amount), [Month Number]), -[Month Number])

[P_Kumar]

Thanks  a lot Sunny, it's working for the example fine.  However, my data is as below and seems I am missing something here.

This is how we get data.

Country   Year         Month             Scenario         SKU Code             One_time_Values (it's a measure)

IND            2018           6                      Actual                    1234                                1000

DUB           2018          6                          Actual                  5656                        2000

I am trying to put one time values in KPI, using the expression, but somehow not getting succeeded.  Please advise.  Thanks in advance.

[P_Kumar]

Also Sunny,  I have tweaked the formula for the same example and put it in a KPI but not working somehow.  Please help

[sunny_talwar]

I don't see Country or Year in the sample you have attached?

[P_Kumar]

Sorry that was just sample data.

This is the real data.  How can I tweak my query if I have these many dimensions? Have tried but without success.  Pls help.

[sunny_talwar]

This is your data?

Country   Year         Month             Scenario         SKU Code             One_time_Values (it's a measure)

IND            2018           6                      Actual                    1234                                1000

DUB           2018          6                          Actual                  5656                        2000

What is the expected output?

[P_Kumar]

Thanks.. 

as you can see we have data only for month '6'.  When a user select Country,  Month from any value between 1 to 5, it should show '0' and when user select  Country, Month from any value between month from 6 to 12, it should show the last available value..i.e. Sum(One_time_Values) for corresponding Country.

Country   Year         Month             Scenario         SKU Code             One_time_Values (it's a measure)

IND            2018           6                      Actual                    1234                                1000

DUB           2018          6                          Actual                  5656                        2000

[sunny_talwar]

---

@P_Kumar wrote:

When a user select Country,  Month from any value between 1 to 5, it should show '0'

Okay...

---

@P_Kumar wrote:

when user select  Country, Month from any value between month from 6 to 12, it should show the last available value..i.e. Sum(One_time_Values) for corresponding Country.

---

So, both the above statements are based on when use make a selection in country? I am really lost and have no idea what you mean

[P_Kumar]

ountry   Year         Month             Scenario         SKU Code             One_time_Values (it's a measure)

IND            2018           6                      Actual                    1234                                1000

DUB           2018          6                          Actual                  5656                        2000

Sorry for the confusion.  Country will be selected from 'Country' filter and 'Month' will be from Month filter

eg:

1. If user select 'IND' from 'Country' filter and '8' from 'Month' filter, it should show KPI Value as 1000 (because the nearest available value for month '8' for country 'IND' is 1000.

similarly

2. If user select 'IND' from 'Country' filter and '8' from 'Month' filter, it should show KPI Value as 1000 (because the nearest available value for month '8' for country 'IND' is 2000.