Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago
Solved

I calculate performance in hours between two different time dates

Good morning community, please need the following help.

I have an orders table where there are two different dates/times.

  • Date Time Entry Order and Date Time Dispatch Order
  • I have start time daily hours 08:00 to end time 17:00

The question is that I want to calculate the number of hours between those two different dates/times.

I already have the working days identified in the calendar table.

As I could do to calular for example the following situation.

I enter an order on 07/03/2022 07:50hs and it is dispatched on 10/03/2020 at 16:00hs. The day 08/03/2022 was a holiday (non-working day).

The final result should give me 26 hours because

  1. for more than the day 07/03 the order entry at 07:50 I have to consider the opening hour 08:00hs to 17:00hs = 9hs
  2. is not considered the day 08/03 because it is non-working day = 0hs
  3. are considered all day 09/03 from 08:00 to 17:00hs = 9hs
  4. 10/03 is dispatched at 16hs = 8hs

9 + 0 + 9 + 8 = 26hs

I would appreciate your help.

Thank you

  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Syndicate_Admin ,

     

    Assuming you have created the following Calendar table and sample data, 

     

                 

     

    then please create the following calculated columns.

     

    Bus Hours Open = 
VAR BusHoursStart = CALCULATE ( SELECTEDVALUE ( BusinessHours[Time] ), BusinessHours[Business Hours] = "Start" )
VAR BusHoursEnd = CALCULATE ( SELECTEDVALUE ( BusinessHours[Time] ), BusinessHours[Business Hours] = "End" )
VAR BusHoursPerDay = VALUE ( BusHoursEnd - BusHoursStart ) * 24
VAR ThisStartTime = [Time Entry Order]
VAR ThisEndTime = [Time Dispatch Order]
VAR StartDate = [Date Entry Order]
VAR EndDate = [Date Dispatch Order]
VAR FirstDayElapsedTime =
    SWITCH (
        TRUE (),
        RELATED ( 'Calendar'[Working Day] ) = 0, 0,
        ThisStartTime >= BusHoursEnd, 0,
        ThisStartTime <= BusHoursStart, BusHoursPerDay,
        StartDate = EndDate
            && ThisEndTime < BusHoursEnd,
            ROUND ( ( ThisEndTime - ThisStartTime ) * 24, 3 ),
        ROUND ( ( BusHoursEnd - ThisStartTime ) * 24, 3 )
    )
VAR LastDayElapsedTime =
    SWITCH (
        TRUE (),
        LOOKUPVALUE ( 'Calendar'[Working Day], 'Calendar'[Date], EndDate ) = 0, 0,
        ThisEndTime <= BusHoursStart, 0,
        ThisEndTime >= BusHoursEnd, BusHoursPerDay,
        StartDate = EndDate, 0,
        ROUND ( ( ThisEndTime - BusHoursStart ) * 24, 3 )
    )
VAR FullWorkDays =
    CALCULATE (
        SUM ( 'Calendar'[Working Day] ),
        DATESBETWEEN ( 'Calendar'[Date], StartDate + 1, EndDate - 1 )
    )
VAR TotalHours = FirstDayElapsedTime + FullWorkDays * BusHoursPerDay + LastDayElapsedTime
RETURN TotalHours

     

    Referencing: https://exceleratorbi.com.au/calculating-business-hours-using-dax/ 

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Syndicate_Admin ,

     

    Assuming you have created the following Calendar table and sample data, 

     

                 

     

    then please create the following calculated columns.

     

    Bus Hours Open = 
VAR BusHoursStart = CALCULATE ( SELECTEDVALUE ( BusinessHours[Time] ), BusinessHours[Business Hours] = "Start" )
VAR BusHoursEnd = CALCULATE ( SELECTEDVALUE ( BusinessHours[Time] ), BusinessHours[Business Hours] = "End" )
VAR BusHoursPerDay = VALUE ( BusHoursEnd - BusHoursStart ) * 24
VAR ThisStartTime = [Time Entry Order]
VAR ThisEndTime = [Time Dispatch Order]
VAR StartDate = [Date Entry Order]
VAR EndDate = [Date Dispatch Order]
VAR FirstDayElapsedTime =
    SWITCH (
        TRUE (),
        RELATED ( 'Calendar'[Working Day] ) = 0, 0,
        ThisStartTime >= BusHoursEnd, 0,
        ThisStartTime <= BusHoursStart, BusHoursPerDay,
        StartDate = EndDate
            && ThisEndTime < BusHoursEnd,
            ROUND ( ( ThisEndTime - ThisStartTime ) * 24, 3 ),
        ROUND ( ( BusHoursEnd - ThisStartTime ) * 24, 3 )
    )
VAR LastDayElapsedTime =
    SWITCH (
        TRUE (),
        LOOKUPVALUE ( 'Calendar'[Working Day], 'Calendar'[Date], EndDate ) = 0, 0,
        ThisEndTime <= BusHoursStart, 0,
        ThisEndTime >= BusHoursEnd, BusHoursPerDay,
        StartDate = EndDate, 0,
        ROUND ( ( ThisEndTime - BusHoursStart ) * 24, 3 )
    )
VAR FullWorkDays =
    CALCULATE (
        SUM ( 'Calendar'[Working Day] ),
        DATESBETWEEN ( 'Calendar'[Date], StartDate + 1, EndDate - 1 )
    )
VAR TotalHours = FirstDayElapsedTime + FullWorkDays * BusHoursPerDay + LastDayElapsedTime
RETURN TotalHours

     

    Referencing: https://exceleratorbi.com.au/calculating-business-hours-using-dax/ 

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    3 years ago

    You know that the variable created: FirstDayElapsedTime

    It's not working for me, it throws me 0. The other two next ones work correctly. How could I make it work for me? I have the same case as the person raising the problem