Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
I'm just after a bit of guidance in how I can apportion out a full year plan against each month and apply a factor for down time in certain months. It also needs to ensure new starters are factored in i.e. don't get a full year plan and anyone working part time is allocated the correct apportionment. The data is structured as below.
Data Structure
Plan Table
Employee ID
Activity
Days allocated
Staffing Table
Employee ID
Employee name
Start Date
End Date
FTE
Proposed Solution
Slicer by Employee Name
Activity | April | May | June | July | August | September | etc |
Corporate | |||||||
Development | |||||||
Etc |
Current Ideas
Measures
12 separate measures for each month e.g.
April =
IF('Staffing'[Start]<DATE(2023,4,1) && 'Staffing'[End]>DATE(2023,4,30,
SUMX('Plans','Plans'[Days]*0.065),
0)
This currently isn't working and wanted to get this sorted before looking at FTE. Assume I would just added a nested if to consider FTE but if there is a better way any pointers would really be appreciated.
Assumptions
6.5% during April and December and 8.7% for all other months
Thanks in advance for any guidance!
Further update....
In order to address the adjustment to leave I have tweaked the measure above
April = CALCULATE
SUMX('Staffing',((12 / SUM('Staffing'[MonthsPresent])) * 0.065) * SUM('Plans'[Days])),
FILTER('Plans','Plans'[Activity]<>"Leave")
)
I have then just added a new measure for leave (21.67 is days expected per month)
April Leave = 21.67 - 'Plans'[April]
I'm concerned this is a very inflexible and that I currently have 24 separate measures. I imagine there is a much better way to do this.
I am also really struggling with how I can adjust the month factor based on other criteria such as 'Staffing' Location.
Any help would be really appreciated!
Not sure if this will be of use to anyone but think I managed to solve part of the problem, definitely not the most elegant solution....
Created a Calculated Column on the Staffing for number of months present. Then created a measure for each month e.g.
April = ((12 / SUM('Staffing'[MonthsPresent])) * 0.065) * SUM('Plans'[Days])
Still doesn't resolve FTE but thought I'd give an update. Another issue I do have is the factor is applying to all Activities including leave. Ideally, I would want the factor to apply to all Activities except leave and leave adjusted to make the days per month balance but struggling a bit with that.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |