Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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])
Try this for the KPI
Sum({<[Month Number] = {"$(=Max({<scenario = {[*]}, Month, [Month Number] = {[<=$(=Max([Month Number]))]}>} [Month Number]))"}, Month>} Amount)
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])
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.
Also Sunny, I have tweaked the formula for the same example and put it in a KPI but not working somehow. Please help
I don't see Country or Year in the sample you have attached?
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.
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?
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
@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
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.