Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 date | weekday | outsourcer service availability | from | to |
05/22/2020 | Friday | true | 08:00 AM | 00:00 AM |
05/23/2020 | Saturday | false | 08:00 AM | 00:00 AM |
05/24/2020 | Sunday | false | 08:00 AM | 00:00 AM |
05/25/2020 | Monday | true | 08:00 AM | 00: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":
examples | issue time | ticket creation | expected measure (in minutes) |
1 | 05/22/2020 08:10:00 AM | 05/22/2020 08:15:00 AM | 5 |
2 | 05/23/2020 03:12:00 AM | 05/25/2020 08:15:00 AM | 15 |
3 | 05/25/2020 03:10:00 AM | 05/25/2020 08:02:00 AM | 2 |
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
@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.
@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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |