Forum Discussion
Comparing two tables with time data - forecast versus actuals
Hello -
I am looking for some assistance with visualising some timesheet data.
I am trying to visualise the hours planned for activities on our project against what was actually booked. I have created some dummy data to demonstrate what I am looking to acheive.
I have two tables, one containing a plan of activities, assigned to resources, with a time allocated to the activity on a series of dates.
| Date | Staff Number | Name | Activity | Forecast Hours | Rate | Forecast Cost |
| 01/02/2021 | 1 | Luke | Use force | 5 | 50 | 250 |
| 01/02/2021 | 1 | Luke | Lightsabre practice | 3 | 50 | 150 |
| 01/02/2021 | 2 | Han | Shoot First | 1 | 65 | 65 |
| 01/02/2021 | 2 | Han | Rescue Princess | 7 | 65 | 455 |
| 02/02/2021 | 1 | Luke | Use force | 5 | 50 | 250 |
| 02/02/2021 | 1 | Luke | Lightsabre practice | 3 | 50 | 150 |
| 02/02/2021 | 2 | Han | Avoid Jabba | 8 | 65 | 520 |
| 03/02/2021 | 2 | Han | Avoid Jabba | 8 | 65 | 520 |
| 04/02/2021 | 1 | Luke | Use force | 5 | 50 | 250 |
| 04/02/2021 | 1 | Luke | Lightsabre practice | 3 | 50 | 150 |
| 04/02/2021 | 2 | Han | Shoot First | 2 | 65 | 130 |
| 04/02/2021 | 2 | Han | Avoid Jabba | 6 | 65 | 390 |
| 05/02/2021 | 1 | Luke | Use force | 8 | 50 | 400 |
| 05/02/2021 | 2 | Han | Rescue Princess | 3 | 65 | 195 |
| 05/02/2021 | 2 | Han | Avoid Jabba | 5 | 65 | 325 |
The other table is the "actuals", where resources have recorded in our timesheet system what activities they have worked on, for how long. There are no restrictions as to what activities they can record in the system, even if they are not forecasted to do so (which is part of the reason I am looking to visualise this data). Resources can also book more or less hours than required. An example of the actuals data is below:
| Date | Staff Number | Name | Activity | Actual Hours | Rate | Actual Cost |
| 01/02/2021 | 1 | Luke | Use force | 4 | 50 | 200 |
| 01/02/2021 | 1 | Luke | Lightsabre practice | 2 | 50 | 100 |
| 01/02/2021 | 2 | Han | Shoot First | 1 | 65 | 65 |
| 01/02/2021 | 2 | Han | Rescue Princess | 6 | 65 | 390 |
| 02/02/2021 | 1 | Luke | Avoid Jabba | 6 | 50 | 300 |
| 02/02/2021 | 1 | Luke | Lightsabre practice | 2 | 50 | 100 |
| 02/02/2021 | 2 | Han | Avoid Jabba | 7 | 65 | 455 |
| 03/02/2021 | 2 | Han | Avoid Jabba | 9 | 65 | 585 |
| 04/02/2021 | 1 | Luke | Use force | 5 | 50 | 250 |
| 04/02/2021 | 1 | Luke | Lightsabre practice | 3 | 50 | 150 |
| 04/02/2021 | 2 | Han | Shoot First | 2 | 65 | 130 |
| 04/02/2021 | 2 | Han | Avoid Jabba | 6 | 65 | 390 |
| 05/02/2021 | 3 | Chewbacca | Fix Falcon | 8 | 40 | 320 |
| 05/02/2021 | 1 | Luke | Use force | 8 | 50 | 400 |
| 05/02/2021 | 2 | Han | Avoid Jabba | 5 | 65 | 325 |
I have seen on other posts on similar themes that I need an independent date table, which I have created. I have created relationships between the three tables against the date field, shown below:
When I visualise the data, it almost works - the actual hours are correct, but the forecast hours are not correct.
The total number of hours forecast for the time period is correct, but each line seems to replicate the forecast for the day. Also, I have tried to show how tasks that are not in the forecast are shown, e.g. Chewbacca - Fix the Falcon is not in the forecast table, but shows hours in the forecast column.
I hope I have explained my issue. I am new(ish) to PowerBI, so would be extremely grateful for any guidance or suggestions.
Kind regards
Neil
Hi,
In the Dates Table there are blanks appearing in the Date column. Remove those blanks rows.
9 Replies
- Ashish_Mathur
Super User
Hi,
- The relationship between the Forecast/Actual Table and the Dates table should me Many to One with the cardinality set to Single
- Just as you have created a Dates table, create 2 additional tables Name and Activity - each table should only ontain unique entries with no blanks in each table. Create a relationship from the Name/Activity columns of Forecast/Actual tables to the individual tables of Name and Activity
- To your visual drag Dates, Name and Activity from the Calendar and the 2 indiviaul tables that you created
- Now write your measures and drag them to your table.
Thank you Ashish_Mathur for your reply.
I have tried to create a Many to One relationship, but the system won't let me, saying "The cardinality you selected isn't valid for this relationship."
Is there anything you can see that I am doing incorrect?
Kind regards,
Neil
- Ashish_Mathur
Hi,
I'll need to see your PB file. Share the download link.
Hi Ashish_Mathur,
Here is a link to the PBIX file
https://drive.google.com/file/d/15jITA_CuhBDJw_PkCBSP97bZBYiTXm9N/view?usp=sharing
Kind regards,
Neil
