I have 2 tables , one contains actual sales from past 2 years, the other contains forecasted sales for the next 10 years (including the current year).
I have created a measure called Forecast_Volume:
=if([IsForecastValid],
CALCULATE(
SUM(Forecast[MC_5Y_Forecast]),
FILTER(ALL(Forecast[FYName]),
CONTAINS(VALUES('Calendar'[FYName]),'Calendar'[FYName],Forecast[FYName])
),
FILTER(ALL(Forecast[GOLDEN_ID]),
CONTAINS(VALUES(Golden_IDs[GOLDEN_ID]),Golden_IDs[GOLDEN_ID],Forecast[GOLDEN_ID])
),
FILTER(ALL(Forecast[PLANT]),
CONTAINS(VALUES(MASTER_DATA[PLANT]),MASTER_DATA[PLANT],Forecast[PLANT])
)
),
BLANK()
)
(IsForecastValid tests the granularity of the calendar dimension in the pivot table, as the forecast is yearly and actual sales are daily.)
The table that contains the forecast data is called "Forecast".
MC_FY_Forecast = the volume forecast
FYName = the name of the Financial Year
Golden_ID = is the identifier of each product
PLANT = is the identifier of each factory
The date column in Master_Data table (which contains the actual sales) is connected to the date column in the Calendar table.
The FYName column in the Forecast is connected to a "joining table" which contians unique values of FYName, which in turn is connected to FYName in the calendar table. So, both Forecast and Master_Data tables are connected to the Calander either directly or indirectly.
My problem:
My measure is only showing data where the time periods in the forecast and Master_Data tables overlaps, ie. this year.
Any idea why this is happening? I do not have any slicers.
Many thanks, James
