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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alex_Guidi
Regular Visitor

Outsourcer with specific date/time operative windows & SLA calculation - please help

Hi to All,
I am having a very bad time with a problem of SLA calculation of a good outsourcer working for my company. I really hope someone can look at my case and propose a path trough a solution considering I am not a pro on DAX and PowerBI, but I know I need to study to implement an eventual solution this Community can give. Thank you in advance.

 

SCENARIO:
The outsourcer operates from 08:00 AM to 00:00 AM, from Monday to Friday. Because I need to have a flexible calendar where I can modify specific dates/times, I wrote an XLS file which basically has this format, called "calendar.xls" in sheet "outsourcer1":

 

calendar dateweekdayoutsourcer service availabilityfromto
05/22/2020Fridaytrue08:00 AM00:00 AM
05/23/2020Saturdayfalse08:00 AM00:00 AM
05/24/2020Sundayfalse08:00 AM00:00 AM
05/25/2020Mondaytrue08:00 AM00:00 AM

 

The ticketing operations data source is on a separate file XLS coming from a ticketing software which basically has this format and named "tickets.xls" in sheet "tickets": 

examplesissue timeticket creationexpected measure (in minutes)
105/22/2020 08:10:00 AM05/22/2020 08:15:00 AM

5

205/23/2020 03:12:00 AM05/25/2020 08:15:00 AM15
305/25/2020 03:10:00 AM05/25/2020 08:02:00 AM2

 

REQUEST:

I need to calculate the "expected measure (in minutes)" which I fill with expected results, but this is getting me crazy.

 

I figured out should need to create a relationship to the 2 files, use of DATESBETWEEN function, use of Loops to count the dates from/to excluding the outsourcer service availability days and understanding how to consider service time starting from 08:00 in case the issue time is presenting before that hours...  and who know how many other obstacles...

So, please, can anyone of you guys tell me a suggestion of a solution path I could go through? What are the steps you would do for getting the "expected measure (in minutes)" field works? 

I would go through the entire war approaching a Step after Step path, but a global strategic vision is what I am missing... 
Thank you in advance for your support, 
Alex

 

2 REPLIES 2
Greg_Deckler
Super User
Super User

@Alex_Guidi - Can you explain in words how to caculate that column? I am not seeing anything obvious. 

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  Thank you Greg for your time and suggestions and sorry to all the Community for my first try 🙂

 

Sample data as text, use the table tool in the editing bar

 

I attached 2 files which are the real source data's I am operating with:

 

Moon Incidents.xls which is the outsourcer incident container data source. In column O (Issue time) we have the incident date&time that appeared in a monitoring system (do not bother if some records do not have this filled as we started recording this after a while, starting from a certain date they are recorded). In column I (Created) we have the date&time the ticket was created.

 

Outsourcer calendar.xls which is the data source of real service availability of the outsourcer I need to consider in my measure. In column A I have the service date and in column B & C the hours from/to. In column E I have a logical condition where 1 stays for "in service" and a 0 stays for "not in service". 


Can you explain in words how to calculate that column? I am not seeing anything obvious.

What I need is to have a new measure which is basically the column I less O of the "Moon Incidents.xls". At first sight, I would simply use the DATEDIFF function but I can not as I need to consider in the calculation real outsourcer service availability.

 

So I need:

1. To create a relationship between the 2 files linking the column O (Created time) of "Moon Incidents.xls" with the column A (Date) of the "Outsourcer calendar.xls" <-DONE

 

2. To create some sort of loop to run on the "Outsourcer calendar.xls" where I can calculate the eventual days between I & O retrieved from the "Moon incidents.xls" and skipping specific days if the value in column E is 0, but also considering the service hours availability of column B&C (from 08:00 to 00:00) <- THIS IS WHERE I NEED HELP 

 

Expected output from sample data

example 1: For row number 122 of the  "Moon incidents.xls" I expect the measure to be 10 minutes because O (Issue time) is 5/23/2020 6:03:00 AM and I (Created) is 5/25/2020 8:10:00 AM but I wanted to skip days 5/23/2020 and 5/24/2020 because they have 0 in column E of file "Outsourcer calendar.xls", and service start time as 8:00 AM in column B of the same file, so the correct measure is from 08:00 to 08:10 AM, equal to 10 minutes.

 

I hope I might be more clear this time, sorry for the long explanation and thank you in advance for your help, it would really be appreciated,

 

Thank you,

Alex

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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