Forum Discussion
Timephased Data Resource Planning
Hi Power Bi Community,
I have a dataset in excel that keeps a record of roles within an organisation including the date range for each role and when a resource is available for the role. See small sample set below.
I want to create a line graph that shows
line 1) Demand. Where the x axis is time and the y shows how many total staff are required for that day (using columns "post begins from date" and "post end date".
line 2) Capacity. Where the x axis is time and the y axis shows how many total staff are available for that day (using columns "resource available from date" and "post end date".
Expected Result:
Sample Data:
| Department | Role Reference | Post Beginning From Date | Post End Date | Resource Available from Date |
| DEPT 1 | N0001 | 20/02/2023 | 30/03/2024 | 20/02/2023 |
| DEPT 2 | N0002 | 15/05/2022 | 26/08/2023 | 15/05/2022 |
| DEPT 3 | N0003 | 02/12/2022 | 26/08/2024 | 02/12/2022 |
| DEPT 1 | N0004 | 01/05/2023 | 03/03/2025 | 01/05/2023 |
| DEPT 2 | N0005 | 20/07/2023 | 05/12/2023 | 20/07/2023 |
| DEPT 3 | N0006 | 21/08/2022 | 30/05/2024 | 05/09/2022 |
| DEPT 1 | N0007 | 25/06/2022 | 02/02/2024 | 25/06/2022 |
| DEPT 2 | N0008 | 24/03/2023 | 26/07/2023 | 24/03/2023 |
| DEPT 3 | N0009 | 12/02/2023 | 21/07/2024 | 14/03/2023 |
| DEPT 1 | N0010 | 24/09/2023 | 15/11/2023 | 24/09/2023 |
| DEPT 2 | N0011 | 17/01/2023 | 31/08/2024 | 18/03/2023 |
| DEPT 3 | N0012 | 23/03/2022 | 17/11/2023 | 23/03/2022 |
| DEPT 1 | N0013 | 29/01/2022 | 04/07/2023 | 28/02/2022 |
| DEPT 2 | N0014 | 30/03/2023 | 20/03/2024 | 14/04/2023 |
| DEPT 3 | N0015 | 29/07/2022 | 03/11/2023 | 29/07/2022 |
| DEPT 1 | N0016 | 21/01/2022 | 29/07/2022 | 21/04/2022 |
| DEPT 2 | N0017 | 07/02/2022 | 22/11/2022 | 07/02/2022 |
| DEPT 3 | N0018 | 10/08/2022 | 22/07/2023 | 10/08/2022 |
| DEPT 1 | N0019 | 23/11/2023 | 13/12/2024 | 08/12/2023 |
Hi LiamReidy ，
You can refer the following links to achieve it.
Total Number Of Staff Over Time - Power BI Insights
Calculating Employee Attrition with DAX
1. Create one if there is no date dimension table in your model
2. Create a similar measure as below to get the number of available staff and required staff
Number of available staff = VAR _seldate = SELECTEDVALUE ( 'Date'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'table'[staffid] ), FILTER ( 'table', 'table'[post begins from date] <= _seldate && 'table'[post end date] > _seldate ) )3. Create a line chart (Axis: Date field of Date dimension table Values: measures)
If the two links above don't help you get the results you want, could you please provide more sample data (containing staff information, relationships between tables, etc.), the logic of the calculation (how the Demand and Capacity are obtained). It seems that the table data you provided before contains staff information. By the way, what is the real start date column used for? If possible, could you please give me an example of the final result you want based on the existing example data and the calculation logic. Thank you.
Best Regards
4 Replies
LiamReidy , Using a date table (common to both demand and Capacity) you should ve able to build.
To handle two dates, use the approach of HR active employee
thank you, i will try this. Still a bit confused how to apply it as your example shows "start date" and "end date." In my example, i have an extra column "real start date." how do i apply this extra field to show "start date to end date" vs "actual start date" vs "end date."
Also, in my example would i replace employee table and employee id with "job/role" table and "job/role id?"
NB: in my example, start date and end date will be fixed, but real start date will vary based on when a suitable employee is found for the role.
Any more ideas please? 😁
Hi LiamReidy ，
You can refer the following links to achieve it.
Total Number Of Staff Over Time - Power BI Insights
Calculating Employee Attrition with DAX
1. Create one if there is no date dimension table in your model
2. Create a similar measure as below to get the number of available staff and required staff
Number of available staff = VAR _seldate = SELECTEDVALUE ( 'Date'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'table'[staffid] ), FILTER ( 'table', 'table'[post begins from date] <= _seldate && 'table'[post end date] > _seldate ) )3. Create a line chart (Axis: Date field of Date dimension table Values: measures)
If the two links above don't help you get the results you want, could you please provide more sample data (containing staff information, relationships between tables, etc.), the logic of the calculation (how the Demand and Capacity are obtained). It seems that the table data you provided before contains staff information. By the way, what is the real start date column used for? If possible, could you please give me an example of the final result you want based on the existing example data and the calculation logic. Thank you.
Best Regards
