Hi,
You have so many conditions. Your formula may not be proper for you. So I rebuild a new one. Here is the step.
A few defaults:
- Workday from Monday to Friday.
- Work hours are 8:00-11:00 and 13:00-16:30
- Holidays aren’t concerned.
- The formulas below are the calculated column each.
- 9999 here means ERROR. Please correct the errors first before go any further.
There are some names you may need to change. Good luck!
- Let’s check the time in the table first, so we could use less IF condition.
Check =
IF (
Verzuimverloop[StartTime] < Verzuimverloop[EndTime],
IF (
(
Verzuimverloop[StartTime]
< Verzuimverloop[StartTime].[Date] + TIME ( 8, 0, 0 )
)
|| (
Verzuimverloop[StartTime]
> Verzuimverloop[StartTime].[Date] + TIME ( 11, 0, 0 )
&& Verzuimverloop[StartTime]
< Verzuimverloop[StartTime].[Date] + TIME ( 13, 0, 0 )
),
9999,
IF (
(
Verzuimverloop[EndTime]
> Verzuimverloop[EndTime].[Date] + TIME ( 16, 30, 0 )
)
|| (
Verzuimverloop[EndTime]
> Verzuimverloop[EndTime].[Date] + TIME ( 11, 0, 0 )
&& Verzuimverloop[EndTime]
< Verzuimverloop[EndTime].[Date] + TIME ( 13, 0, 0 )
),
9999,
0
)
),
9999
)
2. We calculate the amount of weeks of the leave.
WeekAmount =
IF (
Verzuimverloop[Check] = 0,
WEEKNUM ( Verzuimverloop[EndTime], 2 )
- WEEKNUM ( Verzuimverloop[StartTime], 2 ),
9999
)
3. The total hours from start to end.
TotalHours =
IF (
Verzuimverloop[Check] = 0,
DATEDIFF ( Verzuimverloop[StartTime], Verzuimverloop[EndTime], MINUTE ) / 60,
9999
)
3. Days from start to end.
Days =
IF (
Verzuimverloop[Check] = 0,
DATEDIFF ( Verzuimverloop[StartTime], Verzuimverloop[EndTime], DAY )
- 2
* (
WEEKNUM ( Verzuimverloop[EndTime], 2 )
- WEEKNUM ( Verzuimverloop[StartTime], 2 )
),
9999
)
4. Work hours of leave.
WorkHoursLeave =
VAR st =
TIME ( HOUR ( Verzuimverloop[StartTime] ), MINUTE ( Verzuimverloop[StartTime] ), SECOND ( Verzuimverloop[StartTime] ) )
VAR et =
TIME ( HOUR ( Verzuimverloop[EndTime] ), MINUTE ( Verzuimverloop[EndTime] ), SECOND ( Verzuimverloop[EndTime] ) )
RETURN
IF (
Verzuimverloop[Check] <> 0,
9999,
IF (
Verzuimverloop[Days] = 0,
IF (
et <= TIME ( 11, 0, 0 ),
HOUR ( et - st ),
IF (
et >= TIME ( 13, 0, 0 )
&& st <= TIME ( 11, 0, 0 ),
HOUR ( et - st )
- 2,
HOUR ( et - st )
)
),
IF (
st >= TIME ( 13, 0, 0 )
&& et <= TIME ( 11, 0, 0 ),
Verzuimverloop[TotalHours]
- Verzuimverloop[WeekAmount] * 48
- Verzuimverloop[Days] * 17.5
+ 2,
IF (
st <= TIME ( 11, 0, 0 )
&& et <= TIME ( 11, 0, 0 ),
Verzuimverloop[TotalHours]
- 48 * Verzuimverloop[WeekAmount]
- Verzuimverloop[Days] * 17.5,
IF (
st <= TIME ( 11, 0, 0 )
&& et >= TIME ( 13, 0, 0 ),
Verzuimverloop[TotalHours]
- 48 * Verzuimverloop[WeekAmount]
- Verzuimverloop[Days] * 17.5
- 2,
Verzuimverloop[TotalHours]
- 48 * Verzuimverloop[WeekAmount]
- Verzuimverloop[Days] * 17.5
)
)
)
)
)
Your right because i see that the time someone has called in sick can be on all hours of the day.
I see data with a time of 0:00 so can we change the formula to coop with this ?
it's not likely i change the original data but maybe we can change the formula so that IF the time is 0:00 then use 08.00 ?
This is how my data looks now and it looks similar to yours
but all of the errors are triggerd by dates with a time of 0:00 and sometimes the formula doesn't work right if the startdate has a time of lets say 08:00 and a DueDate of 0:00
Question 2 has been solved in the DB however i still have a 9999 value
CorrectStartTime is 15-5-2017 08:30:00 and the CorrectEndTime is Blank so it reads Today which is 15-5-2017 0:00:00 and that results in the error because EndDate is < StartDate
Some results dont seem to be right if i check the WorkHoursLeave
In this example you can see a couple of strange results
For example, if someone is sick for 1 day it should read 8 hours (given that startdate is 08:00:00)
but in this case someone has been sick from 15-1-16 until 19-1-16 which are 3 workingdays (not 2) and 3x8 hours is 24 and nog 64 hours. (Blue example)
But the Red example is also strange because someont is sick between 22-01-16 (sunday) and 28-01-16 (saturday) which are 5 workingdays and not 4 and 5x8 hours are 40 and not 80
Hi,
You should have a Date Table and an Employee Table. If not, you can create one follow these steps.
1. Create a Date Table and add one column "IsWorkday" later.
Dates =
CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) )
IsWorkday =
IF ( WEEKDAY ( [Date], 2 ) IN { 1, 2, 3, 4, 5 }, 1, 0 )2. Add one column in table Verzuimverloop.
Date = [Datum ziektemelding].[Date]
3. Create relationships. (Showed in the picture.)
4. Create three measures.
TotalLeaveHours =
CALCULATE (
SUM ( Verzuimverloop[WorkHoursLeave] ),
FILTER ( Verzuimverloop, Verzuimverloop[Check] <> 9999 )
)
TotalWorkHours =
TOTALMTD ( SUM ( Dates[IsWorkday] ), Dates[Date] ) * 8
* COUNT ( Employee[People] )
Result =
[TotalLeaveHours] / [TotalWorkHours]5. See the result in the picture.
Is this what you want?
Best Regards!
Dale
You get the point.it looks like we are almost there but i want to show the data in this line graph.
However the results are not in the right format i think and when i change it to 'Percentage' it gets better
however the TotalLeaveHours changes if i change the visual TimeLine but the TotalWorkHours remain the same.
Obviously i want to have both values change if he change the 'Timeline' visual for example a month.
Ive been struggling with the relationships because i think that is where they problem lies but i dont know how to fix it.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
