Forum Discussion
Tracking active jobs
Hi all,
I have data around jobs that are used on our systems, and I'd like to plot these out on a line graph, but I'd like to line graph to display when the various jobs started, and then also keep track of how many jobs were active at the time - this is to help identify when numerous jobs overlap and there's a potential pinch point because of it.
The data I have is name, start time (date/time) and duration - occasionally due to errors a job can run for over 24 hours, this happens very rarely, but I'd like to put in a failsafe that would capture and report on these rather than ignoring them.
I can plot out how to account for the new jobs, and how to extract the time for the date column, adding the duration to the time to give an end time, etc - but what measures do I need to consider to effectively take into account the active jobs at any time, e.g. something like the example below:
| Jobname | Start | Duration | Active jobs |
| abc | 02/08/2024 10:00:00 | 00:45:00 | 1 |
| def | 02/08/2024 10:30:00 | 00:30:00 | 2 |
| ghi | 02/08/2024 12:00:00 | 00:05:00 | 1 |
Step 0: I use your data and a 'Calendar' data below.
Step 1: I edit 'DATA' table on Power Query Editor.
- After -
Step 2: I make a measure and a graph.
M_Active Jobs = CALCULATE(SUM([Active jobs 2]),FILTER(ALLSELECTED('Calendar'),'Calendar'[Date Time]<=MAX('Calendar'[Date Time])))
4 Replies
Step 0: I use your data and a 'Calendar' data below.
Step 1: I edit 'DATA' table on Power Query Editor.
- After -
Step 2: I make a measure and a graph.
M_Active Jobs = CALCULATE(SUM([Active jobs 2]),FILTER(ALLSELECTED('Calendar'),'Calendar'[Date Time]<=MAX('Calendar'[Date Time])))
This works perfectly - thanks
What's the required granulatity level? Minutes? Seconds? From your description I assume jobs can cross day boundaries?
The standard approach is to prepare a granularity table and then use cross joins and INTERSECT to capture the time units covered by each job.
Or -you could use a graphical solution with a Deneb bar chart.
Thank you for this - the other poster provided a solution that worked, but thank you for responding.
I had not heard of the INTERSECT command before, but will look into it for future reference.
