How to prevent double-counting of old forecast months that have since become actuals
Here's my scenario:
It's September and I have the following financial data in my model: - Actuals (up to and including August) - Forecast 9: forecast data for September onwards - Forecast 7: an older forecast from July, which has the then 'forecast' data for July onwards
Actuals and Forecast 9 splice together perfectly and give a continuous monthly profile.
However, if I want to show Actuals and Forecast 7, both data sets include data for July and August. I want to see the picture as it was then, so need to prevent the Actuals for July and August from displaying.
It gets more complicated as I have many more historical forecasts (Forecast 6, 5, 4, etc.), but the above is the essence of the problem. Also, the actuals are more granular than the forecasts, so they currently sit in separate fact tables.
How is best to prevent the double-counting of old forecast months that have since become actuals too? These double-counted months would be different for each old forecast.
@_Adrian - So, making some assumptions about things, maybe try:
VAR __Date = MAX('Calendar'[Date])
VAR __ActualsMax = MAXX(ALL('Actuals'),[Date])
__Date > __ActualsMax,
Honestly though I have no idea what you are trying to do, create a new table, or what. The concept above assumes a seperate date table, you figure out if the date is more or less than the maximum date in your Actuals table, if so, return the forecast number, otherwise return the actuals number. Avoids adding both together.
I want to include values (Units, or Gross Sales, or Rebates) from both tables such that I have a continuous time line of actuals and forecast data, for each forecast type, that I can use in time intelligence measures (YTD, YoY etc.).
I can envisage a measure for each forecast type, say 'Actuals & Forecast 6' that:
- includes actuals until the forecast starts and then forecast values thereafter. So for Forecast6 (which has forecast values from June), it would have Actuals until May (month 5) then Forecast6 values from June onwards.
- excludes the Actuals values for months 6, 7 and 8.
However, each month there's a new forecast name, say Forecast9 and I don't want to have to create a new set of measures every month.
So far I have some simple measures, for example:
Gross Sales = SUM ( Actuals[Gross Sales amount] ) + SUM ( Forecasts[Gross Sales amount] )
These can be used with a slicer on 'Actuals or Forecast Type' and choosing 'Actuals' and 'Forecast6' for example. Aside from the double count issue already mentioned, there's a limitation with this; I can see the business then asking for a side by side comparison between 'Actuals & Forecast6' and 'Actuals & Forecast7' which I can't show with slicers as it would sum Actuals + Forecast6 + Forecast7.
Maybe my approach/understanding is wrong and there's a better way of doing this - suggestions welcomed! And also any help with the DAX for excluding those overlap Actuals months would be great.
I hope that explains the problem a little better. If not, just let me know what's unclear and I'll try to clarify.