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 Guys
I am working on greating a staff utilisation report which looks at;
1. actual hours logged vs expect hours as a percentage
2. expected hours to be pro rata if staff leaver or join in that period from;
a) hire date to last day of the month
b) start date to termination date
I am coming stuck on point 2, I can share my data if someone wants to have a look. The formula I am using is below
Total Expected = if('Timecard Split'[Year-Mon - LDoTM]='Timecard Split'[Year-Mon - Hire Date],CALCULATE(sum('Calendar'[Weekday?]),DATESBETWEEN('Calendar'[Date].[Date],'Timecard Split'[Hire Date],'Timecard Split'[Year-Mon - LDoTM]))*8,if('Timecard Split'[Year-Mon - LDoTM]='Timecard Split'[Year-Mon - Termin. Date],CALCULATE(sum('Calendar'[Weekday?]),DATESBETWEEN('Calendar'[Date].[Date],'Timecard Split'[Start of Month],'Timecard Split'[Termination Date]))*8,IF(AND('Timecard Split'[Year-Mon - LDoTM]<>'Timecard Split'[Year-Mon - Hire Date],'Timecard Split'[Year-Mon - LDoTM]<>'Timecard Split'[Year-Mon - Termin. Date]),'Timecard Split'[ExpectedMonthHOurs])))
LDoTM = last day of the month
Also let me know if you need anything else to add.
Many thanks
Ebrima
Solved! Go to Solution.
HI @4c_em,
It seems like you calculate available working hours between two date column, I'd like to suggest you to take a look at below link which has similar requirement:
Regards,
Xiaoxin Sheng
HI @4c_em,
It seems like you calculate available working hours between two date column, I'd like to suggest you to take a look at below link which has similar requirement:
Regards,
Xiaoxin Sheng
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |