Need Help Calculating Variance Between Budget and Sum of Transactions Across Two Tables

[tpcao]

I have two tables, a budget/prediction and an actuals table. I want a summary table that adds up all the transactions in the actuals table and compares it to the budget table to find the variance between the two tables. Please see below for an example data set.

Prediction Table
Month ID	Date	Group	Monthly Value
1/1/2020A	1/1/2020	A	1000
1/1/2020B	1/1/2020	B	5000
2/1/2020A	2/1/2020	A	8000
2/1/2020B	2/1/2020	B	6000

Actuals Table
Month ID	Date	Group	Monthly Value
1/1/2020A	1/8/2020	A	350
1/1/2020A	1/11/2020	A	200
1/1/2020B	1/11/2020	B	755
1/1/2020A	1/19/2020	A	430
1/1/2020B	1/20/2020	B	3000
1/1/2020A	1/23/2020	A	90
1/1/2020B	1/29/2020	B	705
2/1/2020A	2/1/2020	A	2560
2/1/2020B	2/8/2020	B	340
2/1/2020B	2/9/2020	B	270
2/1/2020A	2/17/2020	A	4750
2/1/2020B	2/22/2020	B	3600
2/1/2020A	2/23/2020	A	300
2/1/2020B	2/27/2020	B	1870

Summary Table
Month ID	Prediction	Actual	Variance
1/1/2020A	1000	1070	70
1/1/2020B	5000	4460	-540
2/1/2020A	8000	7610	-390
2/1/2020B	6000	6080	80

Currently the two tables are connected with a one-to-many relationship by Month ID, with the Prediction table being the one and the Actuals table being the many. Can anybody help me with how to do this?

[amitchandak]

@tpcao , better to create two-dimension table Date(have a month there) and Group and join them with both facts.

For Group - https://www.youtube.com/watch?v=Bkf35Roman8

Date -To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[wdx223_Daniel]

@tpcao the best way may be to create a date table and link these tables by date. but here is a solution just base on your two tables

[tpcao]

I tried to use your solution, however when I tried to write out the DAX expression, when I got the Actuals portion of the summarize function, Power BI wouldn't let me choose those columns. I think I'll try yours and Amitchandak's suggestion and try joining by dimension tables and work from there.