Forum Discussion
Forecast accuracy development over time
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
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 () )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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 () )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.