I have a dashboard on SSAS model (Direct Query) that is build up on a view of 2 tables. one of the table represents Sales history with a blank forecast date and the other table represents the forecast with multiple forecast dates. I would like to have a comparision of Sales history Vs Forecast for all of the forecast dates.
Data is stored as below in the SSAS Model :
The dashboard should look like this. how do show a value for history even though the rows with history doesnt have a date.
I was able to show the data as expected with the allexcept however how can I ignore rowcontext but include filter on slicer. Below is my report. I'm able to show the history which has Blank forecast dates even when the forecast date is not selected. My formula includes filter on start month and product line. Everything works as expected. However I want to use the weighted average of the total history selected in the report. But the report context is coming into picture here. Last column is the sum of the history for the selections made. I want to show the sum as 159405 in all rows. However the sum is shown 27108 for 003 and 132297 for 006. How can I override the row context but make sure the product line filter in the slicer is applied. Below is the formula I have for the measure test
test = CALCULATE(SUM('V_FORECASTACCURACY'[DeliveryQty in SU]), ALLEXCEPT(V_FORECASTACCURACy,V_FORECASTACCURACY[PROFITCENTERBRAND],V_FORECASTACCURACY[STARTMONTH],V_FORECASTACCURACY[STARTWEEk],US_MATERIAL[Product Line]))