_____________________________________________________________________________________________________________________________________
Workday Seconds =
VAR vWorkdayStartHour = 8
VAR vWorkdayEndHour = 17
VAR vColumn1 = bw_Cofiwerkorders[Start_werk]
VAR vColumn2 = bw_Cofiwerkorders[Datum_afgehandeld]
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
___________________________________________________________________________________________________________________________________
When I want to convert this formula to Power Query, it gives me errors on CALCULATE and COUNTROWS.
Are there any alternatives for this part of the formula, so it works in Power Query?