Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Icey
Community Support
Community Support

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

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
Community Support
Community Support

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

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.

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.