How do I dance with time?

[Anonymous]

Well, apologies for the Subject but I really need some help here.

I have the below scenerio.

Order is placed on 28-Apr1500 & delivered on 29-Apr 1300. Target is to deliver within 12 hours ie 29-Apr 1500. If it is urgent, it needs to be delivered within 6 hours.

I simply want to show 2 things on a dashboard

1) The number of orders that are due today and at what time.

2) How many orders were delivered within target for today against total orders delivered.

All of this considering working hours, weekends and holidays.

Below are the columns in my data table. I also have a date table identifying if a day is a work-day or not.

Date table:

Below is a diagram of the entire thing:

How do I dance with this data?

[jsaunders_zero9]

Hi @Anonymous,

This is an interesting scenario you have, I would be glad to take a look. Could you provide a file with some data that I can play with? Are you looking to complete this is DAX or PowerQuery or indifferent?

[Anonymous]

Hi @jsaunders_zero9 

Thank you for the help. As my dashboard & data is in Turkish, I have re-created the dashboard from a .pbit file and linked it to a sample excel file. Attaching the .pbix file. You migh find text in Turkish so please ignore. I have tried to translate to Turkish where necessary.

Below is a screenshot of my dashboard and the Red areas are where I plan to add my visualizations.

I have put the report on my GoogleDrive as I think I cannot attach it to this post. Link is: 

https://drive.google.com/drive/folders/15Rsm3LyzawHwjCuHQCrxARq1UpKqfIp1?usp=sharing 

[AilleryO]

Hi,

Cannot acces to your file, authorization required. Can you share it ?

You'll need a time table on top of date table to compute calculations. 

[Anonymous]

Hi- I have updated the link. You should be able to download the pbix .file.

[AilleryO]

Can you share as well your xlsx file ?

I was wondering if your planned closed date was calculated by you or is coming from the source but I cannot access the source since I do not have it 😞

[Anonymous]

Hello,

My apologies, I have uploaded the excel as well.

Planned Closed is just a date/time value that is selected from the tool. It sends a reminder to the assignee.

I wanted to find how how many tickets are due to breach Response & Resolution SLA today. Also how much time is left and how many have breached this month.

Thanks in advance!

[Anonymous]

@jsaunders_zero9  or anyone who can help?