Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 () )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
Thanks Icey!
You nailed it! - im very happy for this solution. Thanks also for including your pbix that was very helpful.
Cheers
Martin
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |