Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
This might help: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration-Working-Hours/m-p/481543#M...
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.
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
112 | |
96 | |
96 | |
75 | |
72 |