cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Martin_Svedberg Frequent Visitor
Frequent Visitor

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.

Chart 2.PNG

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. 

 

Chart 1.PNG

 

 

 

 

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Icey Super Contributor
Super Contributor

Re: Forecast accuracy development over time

Hi @Martin_Svedberg ,

 

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 () )

Forecast accuracy development over time.PNG

 

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.

 

View solution in original post

2 REPLIES 2
Icey Super Contributor
Super Contributor

Re: Forecast accuracy development over time

Hi @Martin_Svedberg ,

 

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 () )

Forecast accuracy development over time.PNG

 

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.

 

View solution in original post

Martin_Svedberg Frequent Visitor
Frequent Visitor

Re: Forecast accuracy development over time

Thanks Icey!

 

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

 

Cheers

Martin 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,820)