Calculate a MAPE based in several conditions

[jessicarocha]

I would like to calculate the MAPE between a forecasted value and an actual value. 

The problem that I can't solve is that I have a table with historized forecast. What I mean by that is that my forecast is monthly and cover one year period. Every time a new month is over, a new data point is given to the model and the forecast is recalculated until the end of the year. We keep a history from what the first forecast was when there were no data points of the business year being predicted. Then, the forecast that contained one point (month) from the year being predicted and so on. 

Example: the first forecast was done containing sales until 06/21. It predicted 12 sales points in the future. Thus, it forecast what the sales would be for 07/21, 08/21, ...., 06/22. 
Then, once July was completed and we had the actual sales of July, a new forecast was performed including this new information in the model. Thus, we had a new forecast starting from 08/21, ..., 06/22. 
Now we are in September, so we already know what the sales for August was. Thus, we created a new model containing the actual sales from 08/21. Thus, the new forecast starts from 09/21, ..., 06/22.

The main goal is try to predict what total sales for the business year will be. 

So, in order to calculate the MAPE I need to extract data from 3 tables (Date, Current Information, Historized Information). Table Date has 1 - many relationship to the other 2 tables. 

Table Current Information (important columns only, there are many more in the model):

Date		Actual Sales		Current Forecast
01.01.2010		80		NA
...		...		NA
01.06.2021		300		NA
01.07.2021		291		NA
01.08.2021		310		NA
01.09.2021		NA		350
...		NA		...
01.06.2021		NA		270

Table Historized Forecast (important columns only, there are many more in the model):

Date		Type of the sales		Sales		Forecast contains data until
01.07.2021		Forecasted		314		01.06.2021
01.08.2021		Forecasted		315		01.06.2021
01.09.2021		Forecasted		360		01.06.2021
....		Forecasted		..		01.06.2021
01.06.2022		Forecasted		270		01.06.2021
01.07.2021		Actual Sales		291		01.07.2021
01.08.2021		Forecasted		300		01.07.2021
01.09.2021		Forecasted		345		01.07.2021
...		Forecasted		....		01.07.2021
01.06.2022		Forecasted		255		01.07.2021
01.07.2021		Actual Sales		291		01.08.2021
01.08.2021		Actual Sales		310		01.08.2021
01.09.2021		Forecasted		350		01.08.2021
...		Forecasted		...		...
01.06.2022		Forecasted		270		01.08.2021

So, I need to do some condition to take the actual sales from August and calculate the difference using the Forecast that contains data until the previous month, July. Then, I need take the actual sales from July and calculate the difference using the Forecast that contains data until June. 

Example: 
For Date 01.07.2021

abs (291 (Actual Sales July) - 314 (Forecast value for July from Forecast that contains data until June) ) / 291 = 7.9%

For Date 01.08.2021

abs (310 (Actual Sales August) - 300 (Forecast value for August from Forecast that contains data until July) ) / 310= 3.2%

[lbendlin]

I think this would be a good use case for the COALESCE() function, like 

COALESCE(Actuals,Forecast)

So that you take the actuals where they exist, and the forecast otherwise.

Are  you planning to use your own forecast numbers or have you considered using the AI forecast options in Power BI (built in or via R/Python scripts)?

[jessicarocha]

Hi @lbendlin. Thanks for the reply!

I will see how this function works and try to use it. But I do need both the Actuals and Forecast in order to calculate the MAPE. The difficult part for me is that I need different rows according to the "Forecast contains data until".

I was able to calculate it when I create a different measure for the MAPE of each month. But ideally I would not need 12 measures for that. I would like to somehow have it all in one formula. I tried this:

Re: VAR inside a measure returns wrong results but... - Microsoft Power BI Community

but so far it is not working. 

[jessicarocha]

Sorry @lbendlin , forgot to answer the second question. We tried to use the R scripts in Power BI but did not work. We got many errors.

So, for the moment, we calculate the forecast in R. Then export the results as a excel in a sharepoint. Then, this data is used as input for the Power BI report.