Calculate Overlap based on Interval and Frequency

[Anonymous]

My company runs a computer process called a "job" according to an update frequency per minute (minutely) or per hour (hourly). I need to count the overlap of jobs per day according to the interval and frequency.

i.e. at every interval how many jobs are running per minute per day at the specific times indicated by the interval (from "Start of Interval" at beginning of day to "End of Interval" at the end of the day.

I also need to make that overlap into a visual in report view. 

Please Help!!!

[tex628]

This very much depends on how many jobs you have in total?
To properly visualize this you would need to divide each job into seperate minutes, which would mean that there might be as much as 1440 rows for each job. 

If you have too many jobs you could choose to display the frequenzy by 10 min intervals or something similar instead! 

/ Johannes

[Anonymous]

There are 24 jobs with intervals between 1 minute and 2 hours with those specified in the attached screen shot of the table. I have already calculated the number of times a job is run per day in the column "Number of Runs per Day" based on the "Interval(Minutes)" and the "Number of Hours" from "start of interval" to "end of interval". 

I'm trying to determine how many times multiple jobs are running per minute per day and call that the "Count of Overlap".

Do I need to make new columns and/or measures in order to calculate this?

[tex628 · Accepted solution]

This is what i would do considering that you only have 24 jobs, 

I would convert the start  of interval to numeric minutes. 

00:00:00 would be 0 
09:00:00 would be 540
24:00:00 would be 1440 
Etc... 

Next, create a list for each job by using List.Numbers with the new start value. Use number of runs per day as the count and the interval as the increment. 

https://docs.microsoft.com/en-us/powerquery-m/list-numbers

This should give you a row for each time a job is run. So a single job that is run 1440 times should now have 1440 rows.

With this you should be able to display exactly how many jobs are running each minute. 

I hope that you understand! 🙂

Good luck,
Johannes

[v-yuta-msft]

@Anonymous ,

Create a measure like pattern below and drag the measure to the visual.

Overlap =
CALCULATE (
    COUNT ( Table[Job] ),
    ALLEXCEPT ( Table, Table[Interval], Table[Minute], Table[Day] )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Which columns are you referring to in the columns i've marked in RED ?? from this protion of the function:

ALLEXCEPT ( Table, Table[Interval], Table[Minute], Table[Day] )