Actuals vs Forecast in same data

[Anonymous]

Hey all,

I am looking to figure out a way to compare actuals to the forecast from the previous month. Currently, I have all the data in an appended table varying by month. It works well for making nice charts showing the change over time, but I really want to show how accurate our forecast was 30 days ago (eventually I will expand to 60 and 90 days). Below is an example of the structure of the data. I am trying to compare the data of the two bold categories. Thanks in advance!

Month Data Pulled	Date	Forecast or Actual?	Category	Value
2/1/2022	1/1/2022	Actuals	A	10
2/1/2022	2/1/2022	Forecast	A	10
2/1/2022	3/1/2022	Forecast	A	10
2/1/2022	1/1/2022	Actuals	B	20
2/1/2022	2/1/2022	Forecast	B	20
2/1/2022	3/1/2022	Forecast	B	20
3/1/2022	1/1/2022	Actuals	A	10
3/1/2022	2/1/2022	Actuals	A	11
3/1/2022	3/1/2022	Forecast	A	12
3/1/2022	1/1/2022	Actuals	B	20
3/1/2022	2/1/2022	Actuals	B	21
3/1/2022	3/1/2022	Forecast	B	21

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to your description, you want to compare the data of the two bold categories? Here are my steps you can follow as a solution.

(1)This is my test data.

(2)We can create  a measure.

Pre_Month_actuals = var _current_date = SELECTEDVALUE('Table'[Month Data Pulled])

var _pre= EOMONTH(_current_date , -2)+1

return

SUMX( FILTER('Table', 'Table'[Date]= _pre) , [Value])

Pre_Month_Forcast = var _current_date = SELECTEDVALUE('Table'[Month Data Pulled])

var _ca=SELECTEDVALUE('Table'[Category])

var _pre= EOMONTH(_current_date , -2)+1

var _f=FILTER(ALLSELECTED('Table'),'Table'[Month Data Pulled]= _pre && 'Table'[Date]= _pre && 'Table'[Forecast or Actual?]="Forecast" && 'Table'[Category]=_ca)

return

SUMX(_f,[Value])

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.