Re: Deriving forecast accuracy and bias from raw data by SKU level

powerbiuser223 — Mon, 24 Jul 2023 07:39:56 GMT

Hi, here are the sample data and the goal of the dashboard - to get forecast accuracy (and eventually bias)

https://docs.google.com/spreadsheets/d/e/2PACX-1vRSa3nOxPqP4mqzqXSWa_k8yxehvSPGfp88DDnZLr1cq-Et_yFSrSmCvnI9ouzXvQ/pub?output=xlsx

Deriving forecast accuracy and bias from raw data by SKU level

powerbiuser223 — Wed, 12 Jul 2023 09:44:16 GMT

Hi everyone,

I am looking for help to get forecast accuracy from the raw data below and I need it to be dynamic/easy to refresh when new data comes in every month.

Here are some points to note:

- Forecast accuracy = 1 - (sum of ABS error / sum of sales)

- Bias = (sum of forecast / sum of sales) - 1

- New data will be added in the following month (i.e. July 2023 submission which consist of forecast for month of July onwards to Dec 2023 for the same SKU)

- I want to look at different period of forecast accuracy:

Lag 1 = June vs May

Lag 2 = June vs April

Lag 3 = June vs March

Hence, when July 2023 submission comes in, I need the forecast accuracy to be automatically changed to:

Lag 1 = July vs June

Lag 2 = July vs May

Lag 3= July vs April

- In the dashboard, I do want to see forecast accuracy & bias by SKU, by country and all other categories the SKU have (which is not present in this simplified table)

Should I do the calculations with DAX in measures or Powerquery? And how should I go about doing it. I am not sure where to begin.

Note: this is incomplete data. There is more than 1 SKU but the same logic applies.

powerbiuser223 Tracker

Re: Deriving forecast accuracy and bias from raw data by SKU level

Deriving forecast accuracy and bias from raw data by SKU level