cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Column to calculate Business hour between two dates excl holidays

I aready have found a DAX script to determine business hours between two dates:

Busines Hours =
VAR _WorkStart =
TIME( 9, 00, 0 )
VAR _WorkFinish =
TIME( 17, 00, 0 )
VAR _1DayWorkingTime =
DATEDIFF( _WorkStart, _WorkFinish, HOUR )
VAR _StartDate =
'All Closed Tickets'[Opened Date Only] // Set the start Date column here
VAR _EndDate =
'All Closed Tickets'[Closed Date Only] // Set the End Date column here
VAR _StartDateTime =
'All Closed Tickets'[Begin Time] //  Set the start Time column here
VAR _EndDateTime =
'All Closed Tickets'[End Time] //  Set the End Time column here
VAR _DaysBetweenStartFinish =
ADDCOLUMNS( CALENDAR( _StartDate, _EndDate ), "DayN", WEEKDAY( [Date], 2 ) ) // You can use the Date table here
VAR _WorkingDaysBetweenStartFinish =
COUNTX(FILTER(_DaysBetweenStartFinish,
[Date] > _StartDate && [Date] < _EndDate && [DayN] < 6),
[DayN]) // Sunday and Saturday are weekend days in this calculation
VAR _Day1WorkingHour =
IF(WEEKDAY( _StartDate, 2 ) < 6,
( MAX( _WorkFinish, _StartDateTime ) - MAX( _StartDateTime, _WorkStart ) ) * 24,
0)
VAR _LastDayWorkingHour =
IF(WEEKDAY( _EndDate, 2 ) < 6,
( MIN( _WorkFinish, _EndDateTime ) - MIN( _EndDateTime, _WorkStart ) ) * 24,
0)
VAR _Duration =
IF(_StartDate = _EndDate&&WEEKDAY(_StartDate,2)<6,
MAX(( MIN( _WorkFinish, _EndDateTime ) - MAX( _WorkStart, _StartDateTime ) ) * 24,
0),

I also have a data table that incorporates public holidays, and a simply conditional column that determines whether each day is a working day.

What I can't seem to do, is to use my existing date table that laready has this worked out, into the business hours code above.

I assume that it would repalce these lines:

VAR _DaysBetweenStartFinish =
ADDCOLUMNS( CALENDAR( _StartDate, _EndDate ), "DayN", WEEKDAY( [Date], 2 ) ) // You can use the Date table here
VAR _WorkingDaysBetweenStartFinish =
COUNTX(FILTER(_DaysBetweenStartFinish,
[Date] > _StartDate && [Date] < _EndDate && [DayN] < 6),
[DayN]) // Sunday and Saturday are weekend days in this calculation

VAR _WorkingDaysBetweenStartFinish =
COUNTX(FILTER('Date Table',
[Date] > _StartDate && [Date] < _EndDate && 'Date Table'[Working Day] = "Yes" ), 'Date Table'[Day Name]) // Count all working days

The "Opened Date Only and "Closed Date only" fields have inactive data connections to the 'Date Table' [Date] field which would necessitate a USERLATIONSHIP as well.

Sadly, I do not know how to incorporate my date table into this code successfully.

Any help greatly appreciated.
1 ACCEPTED SOLUTION
Super User

You can download my sample pbix file to see how to calculate business hours lapsed between two dates. I also have a public holiday calendar to account for business hours. The difference between your approach and mine is to do this in multiple steps instead of one go. (easier to understand the logic).

CRM Leads First Call Action Anaysis.pbix

Proud to be a Super User!

Super User

You can download my sample pbix file to see how to calculate business hours lapsed between two dates. I also have a public holiday calendar to account for business hours. The difference between your approach and mine is to do this in multiple steps instead of one go. (easier to understand the logic).

CRM Leads First Call Action Anaysis.pbix

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors