Business Time

[ben_hargreaves]

Hi, 

I'm looking to create business hours which are 8am to 6pm, excluding bank holidays (BH already in a table) using a custom column in transform data (is this M?). 

have the ticket created times and the resolved times in date/time but tried a few options and haven't progressed in a while. 

Thanks
 

[v-shamiliv]

Hi @ben_hargreaves 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-shamiliv]

Hi @ben_hargreaves 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-shamiliv]

Hi @ben_hargreaves 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-shamiliv · Accepted solution]

Hi @ben_hargreaves 

The error likely comes from how [Date] is used inside _DaysBetweenTable and _WorkingDays. DAX can get confused with column context in these cases, especially when comparing to the bank holiday table.

Try updating _WorkingDays like this:

VAR _WorkingDays =

    COUNTROWS (

        FILTER (

            ADDCOLUMNS (

                _DaysBetweenTable,

                "IsHoliday",

                    CALCULATE (

                        COUNTROWS('Bank Holidays'),

                        'Bank Holidays'[UK Bank Holidays] = [Date]

                    )

            ),

            [DayOfWeek] < 6 && [IsHoliday] = 0

        )

    )

This makes the holiday check more reliable.

To troubleshoot, try returning intermediate values (like _WorkingDays) to isolate the issue.

Let me know the exact error message if you're still stuck happy to help further

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.

[ben_hargreaves]

Changed to DAX after all your advice. 

Here is where I'm currently: 

Business Hours =

VAR _WorkStart = TIME(8, 0, 0)

VAR _WorkFinish = TIME(18, 0, 0)

VAR _StartDate = [Created Date]

VAR _StartTime = [Created Time]

VAR _EndDate = [Resolved Date]

VAR _EndTime = [Resolved Time]

VAR _1DayWorkingTime = DATEDIFF(_WorkStart, _WorkFinish, HOUR)

VAR _SameDayResolution =

    IF (

        _StartDate = _EndDate && WEEKDAY(_StartDate, 2) < 6,

        MAX(0, DATEDIFF(MAX(_StartTime, _WorkStart), MIN(_EndTime, _WorkFinish), HOUR)),

        BLANK()

    )

VAR _DaysBetweenTable =

    IF(

        _StartDate + 1 <= _EndDate - 1,

        ADDCOLUMNS(

            CALENDAR(_StartDate + 1, _EndDate - 1),

            "DayOfWeek", WEEKDAY([Date], 2)

        ),

        ADDCOLUMNS(

    FILTER(CALENDAR(DATE(1900,1,1), DATE(1900,1,1)), FALSE),

    "DayOfWeek", BLANK()

    )

 // returns an empty table if range is invalid

    )

VAR _WorkingDays =

    COUNTX(

        FILTER(

            _DaysBetweenTable,

            [DayOfWeek] < 6 &&

            NOT CONTAINS('Bank Holidays', 'Bank Holidays'[UK Bank Holidays], [Date])

        ),

        [Date]

    )

VAR _Day1WorkingHours =

    IF(

        WEEKDAY(_StartDate, 2) < 6,

        SWITCH(

            TRUE(),

            _StartTime >= _WorkFinish, 0,

            _StartTime <= _WorkStart, _1DayWorkingTime,

            _StartTime < _WorkFinish, DATEDIFF(_StartTime, _WorkFinish, HOUR),

            0

        ),

        0

    )

VAR _LastDayWorkingHours =

    IF(

        WEEKDAY(_EndDate, 2) < 6,

        SWITCH(

            TRUE(),

            _EndTime <= _WorkStart, 0,

            _EndTime >= _WorkFinish, _1DayWorkingTime,

            _EndTime > _WorkStart, DATEDIFF(_WorkStart, _EndTime, HOUR),

            0

        ),

        0

    )

VAR _FullDaysWorkingTime = _WorkingDays * _1DayWorkingTime

VAR _TotalWorkingHours =

    IF(

        NOT ISBLANK(_SameDayResolution),

        _SameDayResolution,

        _FullDaysWorkingTime + _Day1WorkingHours + _LastDayWorkingHours

    )

RETURN

    IF(

        NOT ISBLANK(_StartDate) &&

        NOT ISBLANK(_EndDate) &&

        [Type] = "Change Request",

        _TotalWorkingHours,

        BLANK()

    )
This is still creating an error

[rohit1991]

Hi @ben_hargreaves ,

Yes, what you're trying to achieve is possible in Power Query using M, but it's fairly complex due to the need to iterate over time ranges and exclude weekends and bank holidays. You'd need to write a custom function that calculates the total number of business hours between two datetime values by looping through each potential hour, checking if it's within your defined business window (8 AM to 6 PM), and ensuring the date isn’t a weekend or found in your bank holiday table.

While this is doable, it can be quite performance-intensive and hard to maintain. As an alternative, using DAX may be more manageable for this logic, especially if you use a pre-generated calendar table with working hours pre-flagged. This approach would let you calculate business durations more efficiently within your report visuals or calculated columns. Let me know if you'd like an example of either method.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[v-shamiliv]

Hi @ben_hargreaves 
Thank you for reaching out microsoft fabric community forum.

Yes, “Transform Data” uses Power Query (M language). Calculating business hours (8 AM–6 PM) between two timestamps while excluding weekends and bank holidays is possible, but a bit complex in M. You’d need a custom function that loops through each datetime, checks if it’s within business hours, and filters out holidays (from your table) and weekends.

It’s doable, but if you’re open to using DAX instead, it might be easier to manage and maintain.

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.