Forum Discussion
How to calculate Forecast accuracy
Community Championconvert the abs variance to a column
ABS Variance COLUMN = ABS ( 'Table'[Forecast] - 'Table'[Sold] )
Bias = DIVIDE ( SUM('Table'[Sold]), SUM('Table'[Forecast]), 0 )
Accuracy = 1 - (SUM('Table'[ABS Variance COLUMN]) / SUM('Table'[Forecast] ) )
Hi,
I previously marked this post as solved. In relation to the sample I sent over it was. However after trying to use this solution on my actual data I noticed that I could not put the pieces together. One of my issues is that the actual data is divided into two separet sheets (which is how I retreive the information). See example pictures below.
The actual sales is recorded per day
The Forecast is recorded per week and "Scenario" ( Scenario + Date = forecast for that week )
The forecast is recorded on the first day of the week
I want to be able to calculate the forecast accuracy on different time periods (week/Month/Quarter/Year). But when I try Seans solution above, I think I'm getting the variance for each day. Can I create new tables with calculated columns for Week/Month etc and then summarize the variances in that table, or is there some other way of doing this?
Please have a look at the example and see if you have better luck getting a grip on this.
ForecastSales
Thanks for the help!
- dedelman_clng
These links from SQLBI may be of some help:
http://www.daxpatterns.com/handling-different-granularities/
http://www.sqlbi.com/articles/budget-and-other-data-at-different-granularities-in-powerpivot/
There is also a good example on pp 378-381 of The Definitive Guide to DAX by Russo and Ferrari if you can get your hands on that book.
Thanks for the tip. Looked it through but I can't see anything that would help me. Most things are about measures, and from what I understand I should be using columns to be able to evaluate rows individualy.
Glad to receive any further insights or tips!
- dedelman_clng
A measure can be evaluated row by row if your visualization is used correctly (matrix/table with the row identifiers as the rows).
