It also gives me the possibility to get negative results like this; in hours: -34:13:43 or in seconds: -14543.
An example:
Friday 25-2-2022 at 16:00 and Monday 28-2-2022 at 11:00. The answer needs to be 4 hours(I prefer the answer to be in seconds, so 14400). Because the workday ends at 17:00 on friday, so from 16 it is 1 hour. And starts again at Monday 8:00, till 11 it is 3 hours. So the result is 4 hours.
This is the DAX formula I have used and I would like this formula and result in a Power Query formula:
Workday Seconds =
VAR vWorkdayStartHour = 8
VAR vWorkdayEndHour = 17
VAR vColumn1 = bw_Cofiwerkorders[Startdate/time]
VAR vColumn2 = bw_Cofiwerkorders[Date/timefinished]
VAR vWorkdayHours = vWorkdayEndHour - vWorkdayStartHour
// the earlier of the two columns
VAR vStartDatetime =
MIN ( vColumn1, vColumn2 )
// the later of the two columns
VAR vEndDatetime =
MAX ( vColumn1, vColumn2 )
VAR vStartDate =
INT ( vStartDatetime )
VAR vEndDate =
INT ( vEndDatetime )
// count the workdays between StartDate and EndDate (non-inclusive)
VAR vWorkdaysBetween =
CALCULATE (
COUNTROWS ( Dim_Date ),
Dim_Date[Date] > vStartDate,
Dim_Date[Date] < vEndDate,
'Dim_Date'[Workingdays] = 1
)
// total workday seconds between StartDate and EndDate (non-inclusive)
VAR vWorkdaySecondsBetween = vWorkdaysBetween * vWorkdayHours * 60 * 60
// Start Date beginning of day
VAR vStartDateBOD = vStartDate + TIME ( vWorkdayStartHour, 0, 0 )
// Start Date end of day
VAR vStartDateEOD = vStartDate + TIME ( vWorkdayEndHour, 0, 0 )
// End Date beginning of day
VAR vEndDateBOD = vEndDate + TIME ( vWorkdayStartHour, 0, 0 )
// End Date end of day
VAR vEndDateEOD = vEndDate + TIME ( vWorkdayEndHour, 0, 0 )
VAR vStartDatetimeAdj =
SWITCH ( TRUE (),
vStartDatetime < vStartDateBOD, vStartDateBOD,
vStartDatetime > vStartDateEOD, vStartDateEOD,
vStartDatetime
)
VAR vEndDatetimeAdj =
SWITCH ( TRUE (),
vEndDatetime < vEndDateBOD, vEndDateBOD,
vEndDatetime > vEndDateEOD, vEndDateEOD,
vEndDatetime
)
VAR vStartDateSeconds = DATEDIFF ( vStartDatetimeAdj, vStartDateEOD, SECOND )
VAR vEndDateSeconds = DATEDIFF ( vEndDateBOD, vEndDatetimeAdj, SECOND )
VAR vTotalWorkdaySeconds =
SWITCH ( TRUE (),
vStartDate = vEndDate, DATEDIFF ( vStartDatetimeAdj, vEndDatetimeAdj, SECOND ),
vStartDateSeconds + vWorkdaySecondsBetween + vEndDateSeconds
)
VAR vResult = IF ( vColumn1 < vColumn2, vTotalWorkdaySeconds, vTotalWorkdaySeconds * -1 )
RETURN
vResult
