Connecting tables with different granularity and creating DAX measures that uses figures from both

[ZealandZu]

So I need help regarding tables with different granularity, the join between them with the dax measure subracting between them.

In Table 1, I have a fact table of the tasks employees have booked time too on a particalar day. 

Year/Month	Date	Task	Employee	Hours Booked
2023/06	15/06/23	12345	Julia Roberts	4.7
2023/06	15/06/23	12345	Brad Pitt	5.2
2023/06	15/06/23	67894	Brad Pitt	1.2
2023/06	16/06/23	12345	Julia Roberts	3.3
2023/06	16/06/23	55555	Brad Pitt	8
...	...	..	...	...

Next in Table 2 i have a DIM table with which tasks are part of which project along with status etc

Task	Project	Status
12345	Toyota	Open
67894	Toyota	Open
55555	Honda	Open
33333	Skoda	Closed
...	....	....

These two tables are connected by the task number

I also have a DIM Calendar connected to the fact Table 1 via Date

Now this all rolls up fine. I can put Toyota in a matrix along with the Hours booked column and i get the right totals.

However I also have Table 3 that has forecasted hours by Project

Project	Year/Month	Forecasted Hours
Toyota	2023/05	50
Toyota	2023/06	62
Toyota	2023/07	20
Honda	2023/05	34
Honda	2023/06	100
...	...	...

Now how do i somehow connect these table together so that eventually get a table like this:

Project	Year/Month	Total Hours	Forecasted Hours
Toyota	2023/06	14.4	62
Toyota	2023/07	0	20
Honda	2023/06	3.3	100
...	....	....	....

Which i can then use to create measures/calc columns where i can subtract total hours from the forecast hours, create condional formatting measures etc etc.

Many thanks

[lbendlin · Accepted solution]

You control both fact tables by your dimension tables.  In your visual the columns (Project and Year/Month)  need to come from your dimension tables and the hours data from the fact tables.

see attached

[ZealandZu]

worked perfectly. thankyou.