Forum Discussion

jbrines's avatar
jbrines
Icon for Advocate III rankAdvocate III
7 months ago
Solved

Help working out actual time which includes working hours

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 )
)

5 Replies

  • 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

  • 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.

  • 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.