Help working out actual time which includes working hours

[jbrines]

Hi Guys,

I got help on here to create this code, the issue is that it doesn't take working hours in to consideration, can the code be ammended to take these hours in to consideration.

Working hours

Mon - 8am - 5pm

Tue - 8am - 5pm

Wed 8am - 5pm

Thurs 8am - 5pm

Fri 8am - 3:30pm

Code

Actual Time = 
VAR StartDT = SELECTEDVALUE ( 'Results Data'[Created Date] )
VAR EndDT   = SELECTEDVALUE ( 'Results Data'[Closed Date] )

RETURN
IF (
    ISBLANK ( EndDT ) || EndDT < StartDT,
    BLANK(),
    
    VAR StartDate = INT ( StartDT )
    VAR EndDate   = INT ( EndDT )

    VAR SameDay = StartDate = EndDate

    -- Check working day
    VAR IsStartWorkingDay =
        WEEKDAY ( StartDate, 2 ) <= 5 &&
        NOT ( StartDate IN UKBankHols )

    VAR IsEndWorkingDay =
        WEEKDAY ( EndDate, 2 ) <= 5 &&
        NOT ( EndDate IN UKBankHols )

    -- Full working days between start and end
    VAR FullWorkingDays =
        MAX (
            0,
            NETWORKDAYS ( StartDate, EndDate, 1, UKBankHols )
                - IF ( IsStartWorkingDay, 1, 0 )
                - IF ( IsEndWorkingDay, 1, 0 )
        )

    -- Partial day contributions (calendar hours)
    VAR StartDayFraction =
        IF (
            IsStartWorkingDay,
            1 - ( StartDT - StartDate ),
            0
        )

    VAR EndDayFraction =
        IF (
            IsEndWorkingDay,
            EndDT - EndDate,
            0
        )

    VAR TotalCalendarDays =
        IF (
            SameDay,
            IF ( IsStartWorkingDay, EndDT - StartDT, 0 ),
            FullWorkingDays + StartDayFraction + EndDayFraction
        )

    RETURN
        MAX ( 0, TotalCalendarDays )
)

[v-pnaroju-msft]

Hi @jbrines,

We are glad to hear that your issue has been resolved. Kindly share the solution that resolved it, as this may benefit other members of the community. If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi jbrines,

Thank you for confirming that the formula is working as expected. We appreciate you taking the time to follow up, and we are grateful to @FBergamaschi and @ryan_mayu for their inputs. If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[jbrines · Accepted solution]

Guys, 

On reflection the formula is fine, so I don't now need help thanks and thanks to @ryan_mayu and @FBergamaschi for replying.

[ryan_mayu]

@jbrines 

could you pls provide some sample data and expected output?

[FBergamaschi]

Hi @jbrines 

your code is a measure, of course.

Can you illustrate what the measure purpose is, what is actual outcome is, and what data model it is used in?

This will make it easier to answer you

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI