Help with conditional measures

[nj-matt]

Every month, I am snapping the latest full year forecast ahnd storing as a version # corresponding to the month it was made.

To now measure forecast accuracy. I need to have some sort of flag to indicate which future periods at the time of forecast should be considered vs actual results.

So for example Forecast 2024-01 made in January I can compare all completed months now from Jan to Sep, but for Forecast 2024-02 made in Feb I can only compare Feb months forward.

SO I should be left with something like this -->

	Jan	Feb	Mar	Apr
Forecast 2024-01	YES	YES	YES	YES
Forecast 2024-02	NO	YES	YES	YES
Forecast 2024-03	NO	NO	YES	YES

Right now I have forecast version in a dimension table and calendar time in a seperate time dimension table.

So ideally once done I can compere across forecast versions but only calculate values when it was a period of time flagged as YES for that forecast version.

[Anonymous]

Hi @nj-matt ,

Have you already solved the problem? If so, can you share your solution here and mark the correct answer as standard to help other members find it faster? Thank you very much for your co-operation!

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[rajendraongole1]

Hi @nj-matt - To measure forecast accuracy by comparing forecasts to actual results over time and flagging the appropriate periods based on the forecast month, you'll need to create a dynamic system that assigns flags for each period based on when the forecast was made.

create a calculated column that will dynamically flag whether a specific period should be considered for the comparison

CompareFlag =
IF(
MONTH(ForecastTable[ForecastCreationDate]) <= MONTH(Calendar[Date])
&& YEAR(ForecastTable[ForecastCreationDate]) = YEAR(Calendar[Date]),
"YES",
"NO"
)

You may want to use another condition to ensure you're only comparing actuals for completed months (i.e., months for which actual data exists).

CompareFlag =
IF(
MONTH(ForecastTable[ForecastCreationDate]) <= MONTH(Calendar[Date])
&& YEAR(ForecastTable[ForecastCreationDate]) = YEAR(Calendar[Date])
&& Calendar[ActualAvailable] = TRUE,
"YES",
"NO"
)

Use it as a filter in your visualizations so that only periods flagged as "YES" are used in calculations or displayed.

ForecastAccuracy =
CALCULATE(
[ForecastValue] - [ActualValue],
FILTER(Calendar, Calendar[CompareFlag] = "YES")
)

Add a slicer for Forecast Version to allow users to choose the specific forecast they want to evaluate.
Ensure your time dimension table and forecast table are properly linked, so selecting different forecasts automatically updates the comparison period. 

This approach should help you calculate forecast accuracy dynamically, only using the periods relevant to each forecast version.