Forecast accuracy development over time

[Anonymous]

Hi

Im new to Power BI.

Im trying to create a model that shows forecast accuracy development over time in for example a bar chart.

I have a rolling 12 month sale forecast that updates every month.

Forecast 1 - Issued in January - Covering Jan 2019 -Dec 2019

Forecast 2 - Issued in February - Covering Feb 2019 - Jan 2020

etc.

Actual data on same format

I would like to compare each forecast against the actual outcome of sales. And I like to be able to tell the model how many months of each forecast that should be shown in the chart (for example "only include the first 3 months of the forecast", i.e. for Forecast 1 Jan-Mar and for Forecast 2 Feb-Apr). The question I'd like to answer is "How well did we forecast the 'n' first months of each forecast?"

Below example would show that in

Forecast 1 the actual outcome of January - March (i.e. forecast month 1-3 in forecast 1) was 3% below forecast

Forecast 2 the actual outcome of February - April (forecast month 1-3 in forecast 2) was 2% above forecast

etc. 

Above this a slizer to choose number of months to include (for example forecast month 1, 2, and 3)

I have tried diffent ways but im not getting there.

Any Ideas on how to do this?

Thanks

Martin

[Icey · Accepted solution]

Hi @Anonymous ,

You can do like this:

1. Create a selector table with no relationship between others.

Selector Table =
ADDCOLUMNS (
    VALUES ( Forecast[Forecast month] ),
    "first N months of each forecast", Forecast[Forecast month]
)

2. Create  measures.

Select =
IF (
    HASONEVALUE ( 'Selector Table'[Forecast month] ),
    VALUES ( 'Selector Table'[first N months of each forecast] ),
    BLANK ()
)

Measure =
DIVIDE ( SUM ( Forecast[FC] ) - SUM ( Actual[AT] ), SUM ( Actual[AT] ) )

forecast accuracy =
IF ( MAX ( [Forecast month] ) <= [select], [Measure], BLANK () )

This is my PBIX file.

Best Regards,

Icey

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

[Anonymous]

Thanks Icey!

You nailed it! - im very happy for this solution. Thanks also for including your pbix that was very helpful. 

Cheers

Martin