Help with LOOKUP?

[artfulmunkeey]

Hi all, I am relatively new and struggling with what I believe should be quite straightforward, but no end of tutorials seem to be helping me as I get various syntax errors 😕

I have two tables: Planned Hours sorted by project and date, and HoursBooked, also sorted by project and date (in realtiy this table also contains resourcecodes and projectcode, but for simplicity I have grouped by project for now).

HoursBooked
ProjectCode	TimeByDay	HoursBooked
30001	01/01/2020	50
30001	01/02/2020	100
30001	01/04/2020	150
30001	01/07/2020	75
30002	01/02/2020	100
30002	01/03/2020	200
30002	01/06/2020	150
30003	01/01/2020	50
30003	01/04/2020	75
30003	01/05/2020	25

PlannedHours
ProjectName	TimeByDay	PlannedHours
30001	01/01/2020	50
30001	01/02/2020	100
30001	01/03/2020	100
30001	01/04/2020	100
30001	01/05/2020	100
30001	01/06/2020	100
30001	01/07/2020	100
30001	01/08/2020	50
30001	01/09/2020	100
30001	01/10/2020	150
30001	01/11/2020	50
30001	01/12/2020	100
30002	01/01/2020	50
30002	01/02/2020	100
30002	01/03/2020	150
30002	01/04/2020	50
30002	01/05/2020	100
30002	01/06/2020	100
30002	01/07/2020	100
30002	01/08/2020	100
30002	01/09/2020	100
30002	01/10/2020	100
30002	01/11/2020	50
30002	01/12/2020	50
30003	01/01/2020	100
30003	01/02/2020	100
30003	01/03/2020	100
30003	01/04/2020	100
30003	01/05/2020	100
30003	01/06/2020	100
30003	01/07/2020	100
30003	01/08/2020	50
30003	01/09/2020	50
30003	01/10/2020	100
30003	01/11/2020	100
30003	01/12/2020	150

I am attempting to lookup the actual hours based on project ID, and match it to the planned hours by month in the original table.

Something like the following, so that I can then calculate remaining hours.

Merged Table (LOOKUP)
ProjectName	TimeByDay	PlannedHours	HoursBooked
30001	01/01/2020	50	50
30001	01/02/2020	100	100
30001	01/03/2020	100	0
30001	01/04/2020	100	150
30001	01/05/2020	100	0
30001	01/06/2020	100	0
30001	01/07/2020	100	75
30001	01/08/2020	50	0
30001	01/09/2020	100
30001	01/10/2020	150
30001	01/11/2020	50
30001	01/12/2020	100
30002	01/01/2020	50	0
30002	01/02/2020	100	100
30002	01/03/2020	150	200
30002	01/04/2020	50	0
30002	01/05/2020	100	0
30002	01/06/2020	100	150
30002	01/07/2020	100	0
30002	01/08/2020	100	0
30002	01/09/2020	100
30002	01/10/2020	100
30002	01/11/2020	50
30002	01/12/2020	50
30003	01/01/2020	100	50
30003	01/02/2020	100	0
30003	01/03/2020	100	0
30003	01/04/2020	100	75
30003	01/05/2020	100	25
30003	01/06/2020	100	0
30003	01/07/2020	100	0
30003	01/08/2020	50	0
30003	01/09/2020	50
30003	01/10/2020	100
30003	01/11/2020	100
30003	01/12/2020	150

RemainingHours
Project	TotalPlannedHours	TotalHoursBooked	RemainingHours
30001	1100	375	725
30002	1050	450	600
30003	1150	150	1000

Any help would be greatly appreciated, thanks!

[v-diye-msft]

Hi @artfulmunkeey 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[MFelix · Accepted solution]

Hi @artfulmunkeey 

Believe the best option is to create two dimension tables a project code and a calendar. Then make relationship between these two tables and the other two:

Now create the following measures:

HourBooked = SUM('Hours Booked'[HoursBooked])

HoursPlanned = SUM(HourPlanned[PlannedHours])

RemainingHours = [HoursPlanned] - [HourBooked]

Now if you use the calendar and the projects table in your visualizations alongside with your dimension tables you can create the calculations in any way needed:

Check PBIX file attach.

[artfulmunkeey]

Excellent, many thanks for your help!