I have a Dashboard (Showing 4 KPIs) which contains the 1st Calendar with Day and Month. Only two KPIs are dependent on this calendar and the other two KPIs are dependent on the other Calender (2nd Calendar).
The Problem is when a Client tries to change the calendar on the Dashboard, the figures are wrong for the Third and Fourth KPI, which actually depend on the 2nd Calendar.
I am now trying with a Set Analysis to change the selections in the chart with this Formula
Sum ({<2_Calendar.Year = {"$(=getfieldselections('1_Calendar.Year'))"}, 2_Calendar.Month = {"$(= getfieldselections('1_Calendar.Month'))"}>} SKU_Orders.MaterialChange)
It is off topic, but my advice would be to really think about your data model again.
Is it possible to change the load of the base data so that there is one main date in the model and all 4 KPI's work off one Calendar? Maybe you can do this by adding a 3rd Calendar, which the first 2 link into. 3rd Calendar is the KPI report date.
KPI's should be a specific snapshot in time. If you put them all on one page, what date should they be reporting for?
If you can't change the model and the KPI's are on different sheets, you could put in a macro, so that when a sheet is selected, the Year & Month from Calendar1 are copied to be the Year & Month for Calendar2.
eg. OnActivateSheet Run "SwitchDate1to2"
sub SwitchDate1to2
Activedocument.Fields ("Year").select....
End sub
Someone else would need to help with the rest of the macro though! It is not my speciality 🙂
