Forum Discussion
Forecast accuracy development over time 12months
Hi all,
I refer to bleow thread. I have the same result to purpue.
Pbix provided in the solution is no longer working
Wonder if anyone would replicate the solution and come up with something similar.
https://community.fabric.microsoft.com/t5/Desktop/Forecast-accuracy-development-over-time/m-p/786308
Detail of the problem:
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.
Thanks
5 Replies
Hi KarenQ , here’s a concise plan to replicate the solution:
Data Setup:
- Forecast Table: Columns for Forecast Issued Date, Month, Forecast Month, and Forecast Value.
- Actuals Table: Columns for Month and Actual Value.
Relationships:
- Create a Date Table and link it to both Month columns in the Forecast and Actuals tables.
Key Measures:
- Forecast Error = VAR SelectedMonths = 3VAR ActualSum = SUMX(FILTER(Actuals, Actuals[Month] <= MAX(Forecast[Month])),Actuals[Actual Value])VAR ForecastSum = SUMX(FILTER(Forecast, Forecast[Forecast Month] <= SelectedMonths),Forecast[Forecast Value])RETURN(ActualSum - ForecastSum) / ForecastSum
- Forecast Accuracy = 1 - ABS([Forecast Error])
Visualization:
- Use a bar chart with Forecast Issued Date on the X-axis and Forecast Accuracy on the Y-axis.
- Add a slicer for the number of months (SelectedMonths).
Dynamic Inclusion:
- Create a calculated column for filtering: Include In Chart = IF(Forecast[Forecast Month] <= SelectedMonths, 1, 0)
- Filter visuals to include rows where Include In Chart = 1.This approach dynamically analyzes forecast accuracy for the first n months and visualizes changes over time.
Hi, KarenQ
Can you provide some of the sample data and what you expect the output to be? Please remove any sensitive data in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I cannot understand your question. Please write Excel formulas in the Visual Expected worksheet. I will try to translate those into DAX formulas.
hi Ashish,
I have attached the data and expected visuals in my response to v-zhangtin-msft if you follow the thread.
"
Hi Thanks for your reply.
Below sample data attached.
data.xlsx
KarenQ
"
thanks
KarenQ
That is not what i asked.
