Power BI - Adding/Subtracting to the Working Hours using DAX

Question

I have two tables.

Calendar Table

Date       Holiday
1/1/2019   Yes
1/2/2019   No
1/3/2019   No
1/4/2019   Yes
1/5/2019   No
1/6/2019   Yes
1/7/2019   No
.
.

Data Table

 Request #   CreatedDateTime           ClosedDateTime
    A1          1/2/2019 7:15:00 AM    1/4/2019 7:15:00 AM
    A2          1/2/2019 5:15:00 AM    1/5/2019 7:15:00 AM
    A3          1/1/2019 7:45:00 AM    1/6/2019 7:15:00 AM

The Working Hours are 7 AM - 6 PM

If A Request is created in the non-working hours, then the ticket is moved to the next available working hour. The Calendar table gives the working and non-working days.

The CreatedDateTime_Updated gives the expected result.

Request #   CreatedDateTime          CreatedDateTime_Updated
A1          1/2/2019 7:15:00 AM      1/2/2019 7:15:00 AM
A2          1/2/2019 5:15:00 AM      1/2/2019 7:00:00 AM
A3          1/1/2019 7:45:00 AM      1/2/2019 7:00:00 AM

Now, I am trying to add 33 Working Hours to this column CreatedDateTime_Updated and call that as DueDateTime

Request #   CreatedDateTime_Updated  DueDateTime
A1          1/2/2019 7:15:00 AM      1/7/2019 7:15:00 AM 
A2          1/2/2019 7:00:00 AM      1/5/2019 6:00:00 PM
A3          1/2/2019 7:00:00 AM      1/5/2019 6:00:00 PM

Similar to the CreateDateTime column transformation, the ClosedDateTime also needs to be transformed. but, to the previous working hour, if it is closed in a non - business hour.

Request #   ClosedDateTime             ClosedDateTime_Updated
    A1      1/4/2019 7:15:00 AM        1/3/2019 6:00:00 PM  
    A2      1/5/2019 7:15:00 AM        1/5/2019 7:15:00 AM
    A3      1/6/2019 7:15:00 AM        1/5/2019 6:00:00 AM

Please explain it in terms of single steps, so the formula can be adjusted when needed.

Thank You.

Answer 1

First, create a couple of calculated columns on your calendar table:

WorkHours = IF('Calendar'[Holiday] = "Yes", 0, 11)

and

CumulativeHours =
CALCULATE(
    SUM('Calendar'[WorkHours]),
    ALL('Calendar'),
    'Calendar'[Date] <= EARLIER('Calendar'[Date])
)

I used the calculated column CreatedDateTime_Updated

CreatedDateTime_Updated =
VAR NextWorkDay =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        FILTER ( 'Calendar', 'Calendar'[Date] >= Data[CreatedDateTime].[Date] ),
        'Calendar'[Holiday] = "No"
    )
RETURN
    IF (
        HOUR ( Data[CreatedDateTime] ) < 7
            || NextWorkDay > Data[CreatedDateTime].[Date],
        NextWorkDay + TIME ( 7, 0, 0 ),
        Data[CreatedDateTime]
    )

to create a DueDateTime column:

DueDateTime =
VAR CurrentCumulative =
    LOOKUPVALUE (
        'Calendar'[CumulativeHours],
        'Calendar'[Date], Data[CreatedDateTime_Updated].[Date] - 1
    )
VAR HoursIntoDay =
    ROUND (
           24 * ( Data[CreatedDateTime_Updated] - Data[CreatedDateTime_Updated].[Date] )
            - 7,
        2
    )
VAR DueDate =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        FILTER (
            'Calendar',
            'Calendar'[CumulativeHours] >= CurrentCumulative + HoursIntoDay + 33
        )
    )
RETURN
    IF ( HoursIntoDay > 0, DueDate + ( 7 + HoursIntoDay ) / 24, DueDate + 18 / 24 )

I will try to explain the detail when I have time.