Deriving forecast accuracy and bias from raw data by SKU level

[powerbiuser223]

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.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[powerbiuser223]

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_yFSr...