Forum Discussion
Help with LOOKUP?
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!
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.
3 Replies
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.
Excellent, many thanks for your help!
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.
