Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Pavel_Bazlov
Frequent Visitor

Calculating the duration of event with several overalapping interval filters

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!

6 REPLIES 6
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

Anonymous
Not applicable

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

Best
Darek

Guys, any thoughts on the formula itself?

At this point calculated column would suffice. Any ideas?

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors