Maximum uage count of licenses for the jobs every hour

[Anonymous]

 for eg. for 8-9 I count all the jobs that EXCLUDE below conditions:

        StartTime > 9  OR Endtime <8

This will give me all the jobs that were using the license between 8-9

Although, it doesn't fulfill scenario to exclude the repeated counts of mutually exclusive jobs. This scenario is explained below with an example:

Example: If there are mutually exclusive jobs like

J1 from 8:00 to 8:05 ,

J2 from 8:06 to 8:10 

J3 from 8:11 to 8:15.

By the method explained above for 8-9, it will give count =3 but I want the count as 1 

i.e. excluding mutually exclusive calls because they will be sharing the license

Could you please help me with this?

[Anonymous]

Hi DP86,

I think I was not able to explain my requirement correctly. I will do so now with an example

Id	StartTime	EndTime
93718	10/15/2021 4:00	10/15/2021 4:44	L1
93719	10/15/2021 4:03	10/15/2021 4:06	L2
93720	10/15/2021 4:45	10/15/2021 4:48	L1
93721	10/15/2021 4:45	10/15/2021 4:49	L2
93722	10/15/2021 4:54	10/15/2021 4:56	L1
93723	10/15/2021 4:58	10/15/2021 4:58	L2

So in above table we can see 6 jobs that ran between 4-5 but they consumed only 2 licenses.

Now the problem is jobs table does not hold this information that job J1 is using license L1 and so on.

So we have to reach this count 2 based on the start times and end times only.

Requesting your help

[DP86]

@Anonymous, I think it's me who is not explaining clearly...

I will show MY hypothetical example here:

Id	StartTime	EndTime	overlap jobs	L count
A	10/15/2021 5:00	10/15/2021 5:44	B, C	3
B	10/15/2021 5:03	10/15/2021 5:06	A	2
C	10/15/2021 5:43	10/15/2021 5:48	A, D	3
D	10/15/2021 5:45	10/15/2021 5:49	C, E	3
E	10/15/2021 5:49	10/15/2021 5:56	D	2
F	10/15/2021 5:58	10/15/2021 5:58	-	1

“L count” is number of overlapping jobs + 1.

The maximum number of L count is 3, which would be your max license usage count for the hour.

You can get this table with the following M query:

let
    Source = JobsTable,
    #"Added All" = Table.AddColumn(Source, "All", each Source),
    #"Expanded All" = Table.ExpandTableColumn(#"Added All", "All", {"Id", "StartTime", "EndTime"}, {"Id1", "StartTime1", "EndTime1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded All",{{"StartTime1", type datetime}, {"EndTime1", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([StartTime1] <= [EndTime]) and [EndTime1]>=[StartTime]),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Id", "StartTime", "EndTime"}, {{"L Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

I would use a DAX measure to calculate the max of the "L count" column for the filter context of your choice.

Am I still off-base?

[Anonymous]

Dear @DP86 

Max license count for data shared should be 2 and not 3. Please find below explanation

Id	StartTime	EndTime
A	10/15/2021 5:00	10/15/2021 5:44		L1
B	10/15/2021 5:03	10/15/2021 5:06		L2
C	10/15/2021 5:43	10/15/2021 5:48		L2
D	10/15/2021 5:45	10/15/2021 5:49		L1
E	10/15/2021 5:49	10/15/2021 5:56		L2
F	10/15/2021 5:58	10/15/2021 5:58		L1

Where L1 is for first license and L2 is for second license. This license column is only for explanation and we dont have any such column in db

DAX would be great!

[ronrsnfld]

• Add a custom column which is  an inclusive List at one minute intervals from starttime to endtime
• Delete the other columns
• Expand the List column then Group By Rows and return the Count
• The number of licenses will be equal to the maximum of the count column

In the code below, I did the maximum in M Code, but could easily do it in DAX, depending on what you will be doing with the result.

Using your data, the value returned = 2

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Id", type text}, {"StartTime", type datetime}, {"EndTime", type datetime}}),

//Add column = List of all minutes per ID
    #"Added Custom" = Table.AddColumn(#"Changed Type", "allTimesw", 
        each List.DateTimes([StartTime], Duration.TotalMinutes([EndTime]-[StartTime])+1,#duration(0,0,1,0))),

//Remove the other columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Id","StartTime", "EndTime"}),

//Expand the list to rows
    #"Expanded allTimes" = Table.ExpandListColumn(#"Removed Columns", "allTimesw"),

//Group the times and count the number at each minute
    #"Grouped Rows" = Table.Group(#"Expanded allTimes", {"allTimesw"}, {
        {"Num Licences", each Table.RowCount(_)}    
        }),

//Max license count = max number of rows
    #"Calculated Maximum" = List.Max(#"Grouped Rows"[Num Licences])
in
    #"Calculated Maximum"

[DP86]

Would I be correct if I paraphrased your requirement as "the maximum concurrency of jobs within a specified period"?

If so, you should be able to do this by:

1. couting the overlapping jobs for each job; then
2. getting the maximum of the counts for a specific period

Overlapping jobs (of Job A) are any jobs whose

• start times are not after the ending time of A; and
• end times are not before start time of A.

Does this help?

[Anonymous]

Could you provide a DAX for it. I will try.