Find jobs that were running during specific times

[joyhackett]

Hi!

We have a report that tracks our warehouse ETL jobs.  This is an example table of the jobs:

Job#	Start	End
1	12/15/22 7:00am	12/15/22 8:00am
2	12/15/22 7:23am	12/15/22 7:50am
3	12/15/22 7:45am	12/15/22 8:33am
4	12/15/22 9:44am	12/15/22 10:30am

I am trying to solve for the following:

1. I would like to have a chart that shows how many jobs were running at every minute

Date/Time	# jobs running
12/15/22 7:22 am	1
12/15/22 7:23 am	2
...
12/15/22 7:50 am	3
12/15/22 7:51 am	2

2. I would also like to be able to select MULTIPLE date/times (from a slicer) and see which jobs were running (to look for similarities)
User selects: 12/15/22 7:30am & 12/15/22 10:02am
Results: 

Jobs

1

2

4

Ideally, I would love to have a graph that shows all the jobs running for each minute with the ability to multiselect time ranges (and then return which jobs are in that selection), but I can't find anything on the appstore that exists like this:

Thank you so much for your help in advance! 

🙂 Joy

[joyhackett]

Thank you so much! 

I went a slightly different route and generated a List.DateTime in PowerQuery  and created a new table with all the possible datetimes. Your method is MUCH cleaner!

[amitchandak · Accepted solution]

@joyhackett , You need a date table with date and minute, you can generate that using List.DateTimes

Power Query- List.DateTimes- https://youtu.be/3UBxsIvlcRQ

Give duration as #duration(0,0,1,0)

You can keep that as an independent table and have measures like

CALCULATE(COUNTx(FILTER(Table,Table[Start]<=max('DateTime'[DateTime]) && Table[End]>max('DateTime'[DateTime])),Table[Job #]))

you can refer for formula

Between Dates - Dates between
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...