Hi guys!
I am pretty new to PowerBI and I keep learning, as we try to move there our reports from Excel.
That's how we work:
We forecast our inbound volume each month for next 6 months. Then, as months pass, we have actual number of calls registered for them and we want to check how our forecast was accurate.
I have a problem with Forecast accuracy measure, I'd really appreciate your hints. It's easy to obtain it in Excel, here is how some part of forecast looks like.
| Forecast Date | February 2020 | March 2020 | April 2020 | May 2020 | June 2020 | July 2020 |
| 10.01.2020 | 90 | 110 | 120 | 100 | 140 | 130 |
| 10.02.2020 | | 115 | 125 | 110 | 140 | 130 |
| 10.03.2020 | | | 125 | 105 | 130 | 125 |
| 10.04.2020 | | | | 105 | 125 | 120 |
| 10.05.2020 | | | | | 130 | 120 |
| 10.06.2020 | | | | | | 120 |
And here we have actual numbers of inbound calls:
| Month | Inbound calls |
| February 2020 | 100 |
| March 2020 | 105 |
| April 2020 | 130 |
| May 2020 | 110 |
| June 2020 | 125 |
| July 2020 | 120 |
What I need, is two measures in powerBI - 1) Monthly forecast accuracy (MFA) and 2) Bimonthly forecast accuracy (BFA).
MFA is just Forecast for month/Inbound calls for month, forecasted in previous month.
What's important, forecast number is taken from the previous month forecasted date. It should look like this in Excel.
| Month | Monthly Forecast accuracy | comments |
| February 2020 | 90% | *forecasted in 10.01.2020 |
| March 2020 | 110% | *forecasted in 10.02.2020 |
| April 2020 | 96% | *etc |
| May 2020 | 95% | |
| June 2020 | 104% | |
| July 2020 | 100% | |
So, for February 2020 the result is 90/100 etc.
And here is BFA, where we check the accuracy for two aggregated months:
| Month | Forecast accuracy bimonthly | |
| March 2020 | 98% | *forecasted in 10.01.2020 |
| April 2020 | 102% | *forecasted in 10.02.2020 |
| May 2020 | 96% | *etc |
| June 2020 | 98% | |
| July 2020 | 102% | |
For example, for March 2020 it's [(90+110)/(100+105)]
We take forecasted in 10.01.2020 number of calls for FEB+MARCH, and divide it by inbound calls for FEB+MARCH.
In PowerBI, due to the fact that there may be multiple forecasts for each months I struggle with finding proper DAX formula.
If I put months in rows, then MFA for each of them should be clear once I simply divide Forecast/Inbound calls, however I don't know how to filter forecast to use only the number from forecast made in previous month.
(So, for March 2020, the forecast number should be taken from 10.02.2020). That's my problem.
BFA seems to be even harder.
Do you have any idea how may I resolve it in PowerBI?
I don't know how to upload my .xls file with all this data and metrics formulas, please, advise if you'd need it.
Thanks!
