How can we create business working hours and days in Direct query mode?

[Rockz]

Dear Champions,

I have been trying to create a Business working hours and days in Direct query mode. But I'm not able to use certain functions like SUMX & LOOKUP Value. Is their any alternate syntax which we can use. This is the below measure which Im using.

Working Hours Between Dates =

var startDate = DATEVALUE('Issue Query'[DateCreatedon])

var startTime = DATEVALUE('Issue Query'[DateCreatedon])- startDate

var endDate = DATEVALUE('Issue Query'[DateClosedOn])

var endTime = DATEVALUE('Issue Query'[DateClosedOn]) - endDate

var firstFullDay = startDate + 1

var lastFullDay = endDate - 1

var inBetweenWorkingHours =

    IF(

        firstFullDay > lastFullDay,

        0,

        SUMX(CALENDAR(firstFullDay, lastFullDay), LOOKUPVALUE(WorkingHoursTable[IsWorkingDay], WorkingHoursTable[WeekDay], WEEKDAY([Date], 2)))

    )

var firstDayStart = LOOKUPVALUE(WorkingHoursTable[StartTime], WorkingHoursTable[WeekDay], WEEKDAY(startDate, 2))

var firstDayEnd = LOOKUPVALUE(WorkingHoursTable[EndTime], WorkingHoursTable[WeekDay], WEEKDAY(startDate, 2))

var lastDayStart = LOOKUPVALUE(WorkingHoursTable[StartTime], WorkingHoursTable[WeekDay], WEEKDAY(endDate, 2))

var lastDayEnd = LOOKUPVALUE(WorkingHoursTable[EndTime], WorkingHoursTable[WeekDay], WEEKDAY(endDate, 2))

var effectiveStartTime = IF(startTime < firstDayStart, firstDayStart, startTime)

var effectiveEndTime = IF(endTime > lastDayEnd, lastDayEnd, endTime)

return

    IF(

        startDate = endDate,

        24 * IF(effectiveEndTime > effectiveStartTime, effectiveEndTime - effectiveStartTime, 0),

        var firstDayWorkingHour =

            24 *

            IF(

                startTime > firstDayEnd,

                0,

                firstDayEnd - effectiveStartTime

            )

        var lastDayWorkingHour =

            24 *

            IF(

                endTime < lastDayStart,

                0,

                effectiveEndTime - lastDayStart

            )

        return firstDayWorkingHour + lastDayWorkingHour + inBetweenWorkingHours

    )

Thanks in advance.

[bhanu_gautam]

@Rockz , Try using belowmeasure

Working Hours Between Dates =
VAR startDate = DATEVALUE('Issue Query'[DateCreatedon])
VAR startTime = TIMEVALUE('Issue Query'[DateCreatedon])
VAR endDate = DATEVALUE('Issue Query'[DateClosedOn])
VAR endTime = TIMEVALUE('Issue Query'[DateClosedOn])

VAR firstFullDay = startDate + 1
VAR lastFullDay = endDate - 1

VAR inBetweenWorkingHours =
IF(
firstFullDay > lastFullDay,
0,
CALCULATE(
SUM(WorkingHoursTable[WorkingHours]),
FILTER(
WorkingHoursTable,
WorkingHoursTable[Date] >= firstFullDay &&
WorkingHoursTable[Date] <= lastFullDay &&
WorkingHoursTable[IsWorkingDay] = TRUE()
)
)
)

VAR firstDayStart = CALCULATE(
MAX(WorkingHoursTable[StartTime]),
FILTER(
WorkingHoursTable,
WorkingHoursTable[WeekDay] = WEEKDAY(startDate, 2)
)
)
VAR firstDayEnd = CALCULATE(
MAX(WorkingHoursTable[EndTime]),
FILTER(
WorkingHoursTable,
WorkingHoursTable[WeekDay] = WEEKDAY(startDate, 2)
)
)

VAR lastDayStart = CALCULATE(
MAX(WorkingHoursTable[StartTime]),
FILTER(
WorkingHoursTable,
WorkingHoursTable[WeekDay] = WEEKDAY(endDate, 2)
)
)
VAR lastDayEnd = CALCULATE(
MAX(WorkingHoursTable[EndTime]),
FILTER(
WorkingHoursTable,
WorkingHoursTable[WeekDay] = WEEKDAY(endDate, 2)
)
)

VAR effectiveStartTime = IF(startTime < firstDayStart, firstDayStart, startTime)
VAR effectiveEndTime = IF(endTime > lastDayEnd, lastDayEnd, endTime)

RETURN
IF(
startDate = endDate,
24 * IF(effectiveEndTime > effectiveStartTime, effectiveEndTime - effectiveStartTime, 0),
VAR firstDayWorkingHour =
24 *
IF(
startTime > firstDayEnd,
0,
firstDayEnd - effectiveStartTime
)

VAR lastDayWorkingHour =
24 *
IF(
endTime < lastDayStart,
0,
effectiveEndTime - lastDayStart
)

RETURN firstDayWorkingHour + lastDayWorkingHour + inBetweenWorkingHours
)

[Rockz]

Dear , 

Thanks for your quick response.

But while using this i'm getting  this error

DAX comparison operations do not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

and I need to use true/false condition for working hours table.

This is the measure im using for working working hours table.

WorkingHoursTable =

ADDCOLUMNS (

    CALENDAR (DATE(2020, 1, 1), DATE(2030, 12, 31)),

    "IsWorkingDay", IF (WEEKDAY([Date], 2) < 6, 1,0)

)

Thank you.

[Anonymous]

Hi @Rockz ,

I’d like to acknowledge the valuable input provided by the @bhanu_gautam . Their initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue. 

The problem may be here, your [IsWorkingDay] field is 1 or 0.

VAR inBetweenWorkingHours =
    IF (
        firstFullDay > lastFullDay,
        0,
        CALCULATE (
            SUM ( WorkingHoursTable[WorkingHours] ),
            FILTER (
                WorkingHoursTable,
                WorkingHoursTable[Date] >= firstFullDay
                    && WorkingHoursTable[Date] <= lastFullDay
                    && WorkingHoursTable[IsWorkingDay] = TRUE ()
            )
        )
    )

So you can try to change here to this.

VAR inBetweenWorkingHours =
    IF (
        firstFullDay > lastFullDay,
        0,
        CALCULATE (
            SUM ( WorkingHoursTable[WorkingHours] ),
            FILTER (
                WorkingHoursTable,
                WorkingHoursTable[Date] >= firstFullDay
                    && WorkingHoursTable[Date] <= lastFullDay
                    && WorkingHoursTable[IsWorkingDay] = 1
            )
        )
    )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rockz]

Dear,

I'm getting this error again!

Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.

Working Hours Between Dates =

VAR startDate = DATEVALUE('Issue Query'[DateCreatedon])

VAR startTime = TIMEVALUE('Issue Query'[DateCreatedon])

VAR endDate = DATEVALUE('Issue Query'[DateClosedOn])

VAR endTime = TIMEVALUE('Issue Query'[DateClosedOn])

VAR firstFullDay = startDate + 1

VAR lastFullDay = endDate - 1

VAR inBetweenWorkingHours =

   IF (

       firstFullDay > lastFullDay,

       0,

       CALCULATE (

           SUM (WorkingHoursTable[WorkingHours]),

           FILTER (

               WorkingHoursTable,

               WorkingHoursTable[Date] >= firstFullDay &&

               WorkingHoursTable[Date] <= lastFullDay &&

               WorkingHoursTable[IsWorkingDay] = 1

           )

       )

   )

VAR firstDayStart = CALCULATE(

   MAX(WorkingHoursTable[WorkstartTime]),

   FILTER(

       WorkingHoursTable,

       WorkingHoursTable[IsWorkingDay] = WEEKDAY(startDate, 2)

   )

)

VAR firstDayEnd = CALCULATE(

   MAX(WorkingHoursTable[WorkEndTime]),

   FILTER(

       WorkingHoursTable,

       WorkingHoursTable[IsWorkingDay] = WEEKDAY(startDate, 2)

   )

)

VAR lastDayStart = CALCULATE(

   MAX(WorkingHoursTable[WorkstartTime]),

   FILTER(

       WorkingHoursTable,

       WorkingHoursTable[IsWorkingDay] = WEEKDAY(endDate, 2)

   )

)

VAR lastDayEnd = CALCULATE(

   MAX(WorkingHoursTable[WorkEndTime]),

   FILTER(

       WorkingHoursTable,

       WorkingHoursTable[IsWorkingDay] = WEEKDAY(endDate, 2)

   )

)

VAR effectiveStartTime = IF(startTime < firstDayStart, firstDayStart, startTime)

VAR effectiveEndTime = IF(endTime > lastDayEnd, lastDayEnd, endTime)

RETURN

IF(

   startDate = endDate,

   24 * IF(effectiveEndTime > effectiveStartTime, effectiveEndTime - effectiveStartTime, 0),

   VAR firstDayWorkingHour =

       24 *

       IF(

           startTime > firstDayEnd,

           0,

           firstDayEnd - effectiveStartTime

       )

   VAR lastDayWorkingHour =

       24 *

       IF(

           endTime < lastDayStart,

           0,

           effectiveEndTime - lastDayStart

       )

   RETURN firstDayWorkingHour + lastDayWorkingHour + inBetweenWorkingHours

)

Thanks in advance!.

[Anonymous · Accepted solution]

Hi @Rockz ,

Try using the expression as a MEASURE instead of a calculated column.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.