Calculating the duration of event with several overalapping interval filters

Pavel_Bazlov — Mon, 12 Aug 2019 15:09:07 GMT

Hi,

I have a data model which I am trying to use to calculate KPI's based on Service Level Agreement (IT outsourcing stuff).

The data model contains the following tables:

Ticket History - describes all states in which all tickets were in with all relevant information: state, state start datetime, state end datetime

Ticket ID
Service Group
Assignee
Ticket State
Event Start
Event End

Example:

Ticket ID - Service Group - Assignee - Ticket State - Event Start - Event End

1 - Service Desk - Mark - Assigned - 01.01.2018 08:00:00 - 01.01.2018 09:00:00

1 - Service Desk - Mark - Work in Progress - 01.01.2018 09:00:00 - 09.01.2018 10:00:00

1 - Service Desk - Mark - Solved - 09.01.2018 10:00:00 - 09.01.2018 11:00:00

2 - Datacenter Support - John - Assigned - 01.01.2018 08:00:00 - 01.01.2018 08:30:00

2 - Datacenter Support - Mark - Suspended - 01.01.2018 08:30:00 - 09.01.2018 09:00:00

2 - Datacenter Support - Mark - Work in Progress - 09.01.2018 09:00:00 - 09.01.2018 20:00:00

Calendar - standart date table with calulated columns for year, month, etc.

date
Year
Week day
Day type : working / non-working based on local calendar

Example:

date - Year - Week Day - Day type

01.01.2018 - 2018 - Monday - Non working day

02.01.2018 - 2018 - Tuesday - Non working day

03.01.2018 - 2018 - Wednesday - Non working day

04.01.2018 - 2018 - Thursday - Non working day

05.01.2018 - 2018 - Friday - Non working day

06.01.2018 - 2018 - Saturday - Non working day

07.01.2018 - 2018 - Sunday - Non working day

08.01.2018 - 2018 - Monday - Non working day

09.01.2018 - 2018 - Tuesday - Working day

10.01.2018 - 2018 - Wednesday - Working day

Teams - dimension table grouping Service groups by services.

Team
Service

Example:

Team - Service

Service Desk - 1st line support

Datacenter Support - Datacenter Support

Storage Support - Datacenter Support

Database Support - Datacenter Support

Service Time - table specifies service times for each service.

Service
Day type
Service Start
Service End

Example:

Service - Day type - Service Start - Service End

1st Line Support - Working day - 08:00:00 - 20:00:00

1st line Support - non-working day - N/A - N/A

Datacenter Support - Working day - 00:00:00 - 23:59:59

Datacenter Support - non-Working day - 00:00:00 - 23:59:59

So I need to create a measure (I do not like calculated columns) that will calculate duration of an event in the table Ticket History considering the service time, let's call it duration and the result of it is in minutes:

Ticket ID - Service Group - Assignee - Ticket State - Event Start - Event End - Duration

1 - Service Desk - Mark - Assigned - 01.01.2018 08:00:00 - 01.01.2018 09:00:00 - 0

Comment: as service time for 1st line support service Service Desk service group is a part of is 8:00 to 20:00 for all working days and 01.01.2018 - is not a working day, there are no service hours so the duration is 0

1 - Service Desk - Mark - Work in Progress - 01.01.2018 09:00:00 - 09.01.2018 10:00:00 - 120

Comment: as service time for 1st line support service Service Desk service group is a part of is 8:00 to 20:00 for all working days and 01-08.01.2018 - are not a working days, the only overlapping of service hours and the event period is on 09.01.2018 starting 08:00:00 (service time start for 1st line support) till 10:00:00 (the end of the event) and it is 120 minutes

1 - Service Desk - Mark - Solved - 09.01.2018 10:00:00 - 09.01.2018 11:00:00 - 60

2 - Datacenter Support - John - Assigned - 01.01.2018 08:00:00 - 01.01.2018 08:30:00 - 30

2 - Datacenter Support - Mark - Suspended - 01.01.2018 08:30:00 - 09.01.2018 09:00:00 - 11550

Comment: for Datacenter Support service hours are basically 24х7 so the duration is simply the number of minutes between 01.01.2018 08:30:00 and 09.01.2018 09:00:00 whish is 11550

2 - Datacenter Support - Mark - Work in Progress - 09.01.2018 09:00:00 - 09.01.2018 20:00:00 - 660

I just can't wrap my head around this. Please help!

Re: Calculating the duration of event with several overalapping interval filters

Greg_Deckler — Mon, 12 Aug 2019 17:04:45 GMT

So, if that last table is an example, what would the expected output be?

Re: Calculating the duration of event with several overalapping interval filters

Pavel_Bazlov — Mon, 12 Aug 2019 17:45:33 GMT

The last table is the expected output. As I have stated I need a measure for Ticket History table which will count the duration for each row. As you can see, the last table is the Ticket History table but with a column "Duration" representing the value of the needed measure as I would expect it.

Re: Calculating the duration of event with several overalapping interval filters

Anonymous — Mon, 12 Aug 2019 21:25:51 GMT

Therefore you need a calculated column, not a measure. But this could be done in Power Query, probably even more easily.

Best
Darek

Re: Calculating the duration of event with several overalapping interval filters

Pavel_Bazlov — Tue, 13 Aug 2019 05:43:10 GMT

Guys, any thoughts on the formula itself?

Re: Calculating the duration of event with several overalapping interval filters

Pavel_Bazlov — Wed, 14 Aug 2019 07:30:02 GMT

At this point calculated column would suffice. Any ideas?

Re: Calculating the duration of event with several overalapping interval filters

Pavel_Bazlov — Thu, 22 Aug 2019 07:14:27 GMT

I am trying on my own, to no avail. Any ideas?

topic Re: Calculating the duration of event with several overalapping interval filters in DAX Commands and Tips

Calculating the duration of event with several overalapping interval filters

Re: Calculating the duration of event with several overalapping interval filters

Re: Calculating the duration of event with several overalapping interval filters

Re: Calculating the duration of event with several overalapping interval filters

Re: Calculating the duration of event with several overalapping interval filters

Re: Calculating the duration of event with several overalapping interval filters

Re: Calculating the duration of event with several overalapping interval filters