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
 

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous · 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

Could you please follow below steps:

1. Make a holidays list (type = date): Add a blank query

Holidays = List.Distinct( Table.Column(BH, "Date") )

2. Add a blank query named fnBusinessHours:

(fnStart as datetime, fnEnd as datetime, Holidays as list) as number =>
let
  SH = 8, EH = 18,
  S  = DateTime.From(fnStart),
  E  = DateTime.From(fnEnd),
  days = List.Dates(Date.From(S),
                    Duration.Days(Date.From(E) - Date.From(S)) + 1,
                    #duration(1,0,0,0)),
  isBiz = (d as date) => Date.DayOfWeek(d, Day.Monday) < 5 and not List.Contains(Holidays, d),
  overlap = (d as date) =>
    let a = #datetime(d, SH, 0, 0),
        b = #datetime(d, EH, 0, 0),
        x = if S > a then S else a,
        y = if E < b then E else b
    in  if y > x then y - x else #duration(0,0,0,0),
  dur = List.Accumulate(List.Transform(List.Select(days, each isBiz(_)), each overlap(_)),
                        #duration(0,0,0,0), (s,c)=> s+c)
in Duration.TotalHours(dur)

3. Use it (Add Column >> Custom):

BusinessHours = fnBusinessHours([CreatedDateTime], [ResolvedDateTime], Holidays)

That returns the business hours between Created and Resolved, counting only 08:00–18:00, excluding weekends and your bank holidays.

[Anonymous]

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.