Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Actuals vs Multiple Forecasts

I am trying to prepare matrixes that compare one set of sales actuals to multiple forecast runs to compute for accuracy and variance over time.


In the attached you will find an Excel file that seeds the data:

- Tab test1 has the actual and forecasted values in one table

- Tab test 1A has only the actual values

- Tab test 1B has only the forecasted values

- Of note, the DATE field is the month the data is loaded, and the APPLIED field is when the data is to be applied. For actual values, these dates will be the same, but for the forecasts DATE is when the forecast was created, while APPLIED is for the month the forecast applies to.


In the Power BI file, I have attempted to display the actual values in one matrix, all forecasts in a second matrix and a difference in a third matrix. My challenge is that I do not know how to structure the model/formula in PBI so that the singular actual value is used across all forecasts for a given time period (month).


Any help would be appreciated.



Link to Sample Data 

Super User III
Super User III

@andrewwglenn Read this post to get your answer quickly.

Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!


Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors