Time difference between to date - effective business hour

[Anonymous]

Second try (first post gave an error)

I need to calculate the time in minutes between two dates. I got a message table like this:

Customer A	02/01/2020 11:00	02/01/2020 13:00
Customer B	02/01/2020 12:00	03/01/2020 16:00
Customer C	02/01/2020 16:00	06/01/2020 11:35

But here is the challange. All customers have different contracts in which business hours are described. I need the effective time between the created and solved date. The business hours are stored in a seperated (but linked) table like:

Customer	MonOpen	MonClosed	TueOpen	TueClosed	WedOpen	WedClose	ThuOpen	ThuClose	FriOpen	FriClose	SatOpen	SatClose	SunOpen	SunClose
A	0800	1700	0800	1700	0800	1700	0800	1700	0800	1700	1000	1500	1000	1500
B	0900	1700	0900	1700	0900	1700	0900	1700	0900	1700
C	0000	2359	0000	2359	0000	2359	0000	2359	0000	2359	0600	1800	0600	1800

The customer table can be changed to a better fitting format. It is a csv file, but i can change it with shell or something. 
Expecting the minutes in the message table, preferably in Dax

[Greg_Deckler]

This might help: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration-Working-Hours/m-p/481543#M...

[Anonymous · Accepted solution]

Hi Greg, 

Thanks for the answer. I went another way though. I remembered a post I used a while back to calculate the active days between a ticket created date and close date:
https://community.powerbi.com/t5/Desktop/Calculate-Hours-between-dates-for-only-business-hours/td-p/... 
So i used that to create a table with all the dates a ticket was active. Added a column for the day of the week number and wrote a giant dax stateement to check if the ticket was active and for how long:

Time Active = 
VAR _BusinessOpen = 
SWITCH(
    TRUE(),
    'Active'[DayOfWeek] = 0, RELATED(Customer[MONDAY OPEN]),
    'Active'[DayOfWeek] = 1, RELATED(Customer[TUESDAY OPEN]),
    'Active'[DayOfWeek] = 2, RELATED(Customer[WEDNESDAY OPEN]),
    'Active'[DayOfWeek] = 3, RELATED(Customer[THURSDAY OPEN]),
    'Active'[DayOfWeek] = 4, RELATED(Customer[FRIDAY OPEN]),
    'Active'[DayOfWeek] = 5, RELATED(Customer[SATURDAY OPEN]),
    'Active'[DayOfWeek] = 6, RELATED(Customer[SUNDAY OPEN])
)

VAR _BusinessClose =
SWITCH(
    TRUE(),
    'Active'[DayOfWeek] = 0, RELATED(Customer[MONDAY CLOSE]),
    'Active'[DayOfWeek] = 1, RELATED(Customer[TUESDAY CLOSE]),
    'Active'[DayOfWeek] = 2, RELATED(Customer[WEDNESDAY CLOSE]),
    'Active'[DayOfWeek] = 3, RELATED(Customer[THURSDAY CLOSE]),
    'Active'[DayOfWeek] = 4, RELATED(Customer[FRIDAY CLOSE]),
    'Active'[DayOfWeek] = 5, RELATED(Customer[SATURDAY CLOSE]),
    'Active'[DayOfWeek] = 6, RELATED(Customer[SUNDAY CLOSE])
)

VAR _created =  RELATED(Messages[Start])
VAR _closed = IF(RELATED(Messages[Close])=BLANK(), NOW(), RELATED(Messages[Close]))
VAR _closeddate = IF(RELATED(Messages[Close].[Date])=BLANK(), DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())), RELATED(Messages[Close].[Date]))



VAR _OpenDateHour = 
IF(
    _BusinessOpen = BLANK(),
    BLANK(),
    CONVERT( 'Active'[DateRange] & " " & _BusinessOpen, DATETIME)
)

VAR _CloseDateHour = 
IF(
    _BusinessClose = BLANK(),
    BLANK(),
    CONVERT( 'Active'[DateRange] & " " & _BusinessClose, DATETIME)
)

return
SWITCH(
    TRUE(),
    //Closed? 0 minutes active
    _businessOpen = BLANk(), 0,
    //same day close
    RELATED(Messages[Start].[Date]) = RELATED(Messages[Close].[Date]), DATEDIFF(_created, _closed, MINUTE),
    //First day open. After start businesshour
    'Active'[DateRange].[Date] = RELATED(Messages[Start].[Date]) && RELATED(Messages[Start]) >= _OpenDateHour, DATEDIFF(RELATED(Messages[Start]), _CloseDateHour, MINUTE),
    //First day open. before start businesshour
    'Active'[DateRange].[Date] = RELATED(Messages[Start].[Date]) && RELATED(Messages[Start]) <= _OpenDateHour, DATEDIFF(_OpenDateHour, _CloseDateHour, MINUTE),
    //If day not create or close day
    'Active'[DateRange].[Date] <> RELATED(Messages[Start].[Date]) && 'Active'[DateRange].[Date] <> _closeddate, DATEDIFF(_OpenDateHour,_CloseDateHour, MINUTE),
    //Close day
    'Active'[DateRange].[Date] = _closeddate && _closed <= _CloseDateHour, DATEDIFF(_OpenDateHour, _closed, MINUTE),
    'Active'[DateRange].[Date] = _closeddate && _closed > _CloseDateHour, DATEDIFF(_OpenDateHour, _CloseDateHour, MINUTE),
    'Active'[DateRange].[Date] = _closeddate && _closed = blank(), DATEDIFF(_OpenDateHour, now(), MINUTE),
    BLANK()
)

It's a long piece of dax, but it works fast and perfectly. And it will allow me to quickly work in different time zones and Day light saving times when needed.