Forum Discussion
Timephased Data Resource Planning
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
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? 😁
