Count active "Machines" in context: Handle both Start and end time and time from Context Window

[Anonymous]

Hi!

I have a general problem, that is seemingly hard to solve in Power BI.
I guess a solution to this problem may be usefull to many users.
I have seem related solutions handling cases with dates using ExpandDates, but in this case I need both date and time
(e.g. Power BI Expanding dates within a date range)

The full datamodel and data is provided in the end.

Case:
I have a table with a  list of machines, each row with an
- Machine ID
- Active from datetime
- Active to datetime

🔍I want to display the number of machines that are "Active"* at different times and in different time periods

It is OK to check if a machine is active at a particular timestamp (e.g. 2024.10.16 00:00:00).
But I find is hard to count the number of active machines for each date from the calender table.

🤷‍♂️How to solve this?🤷‍♂️

The solution should also work for more granular time-data then dates (e.g. pr hour)
Below is the current visuals and the desired result, data model and data for all tables.

Below is the datamodel and data for all tables.

In addition to the fact table "Machines", there are two standard tables for date and time:
- calender-table with dates
- time-table with timestamps from 00:00 - 23:59

Table Machines:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lctJCoAwEETRq0ivJfSUyauE3P8apnEnpRCoVX3eGCR0ktTELSmrH2oX89p6WRL31zvPQRqi7QgL0XeEr6a8I3IIgUKxKCEUCsOihjAoHIsWwqHIWPQQGYqChfAnebr89HkD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Active from" = _t, #"Active to" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Active from", type datetime}, {"Active to", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Active from", "Active-from timestamp"}, {"Active to", "Active-to timestamp"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Active-from timestamp", "Active-from timestamp - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Active-from timestamp - Copy", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Active-from timestamp - Copy", "Active-from date"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns1", "Active-to timestamp", "Active-to timestamp - Copy"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Active-to timestamp - Copy", type date}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"Active-to timestamp - Copy", "Active-to date"}})
in
    #"Renamed Columns2"

Table Time (from radacad https://radacad.com/how-to-use-time-and-date-dimensions-in-a-power-bi-model)

let
    Source = List.Times(#time(0,0,0),24*60*60,#duration(0,0,0,1)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Time"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}}),
    #"Inserted Hour" = Table.AddColumn(#"Changed Type", "Hour", each Time.Hour([Time]), Int64.Type),
    #"Inserted Minute" = Table.AddColumn(#"Inserted Hour", "Minute", each Time.Minute([Time]), Int64.Type),
    #"Inserted Second" = Table.AddColumn(#"Inserted Minute", "Second", each Time.Second([Time]), type number),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Second", "AM/PM", each if [Hour] < 12 then "a.m." else "p.m."),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"AM/PM", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Time", "Time - Copy"),
    #"Removed Columns" = Table.RemoveColumns(#"Duplicated Column",{"Time - Copy"}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Removed Columns", "Hour", "Hour - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column1",{{"Hour - Copy", "Hour label"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Hour label", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Hour label"}),
    #"Inserted Prefix" = Table.AddColumn(#"Removed Columns1", "Prefix", each "0" & Text.From([Hour], "en-NZ"), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Prefix",{{"Prefix", "Hour Label"}}),
    #"Extracted Last Characters" = Table.TransformColumns(#"Renamed Columns2", {{"Hour Label", each Text.End(_, 2), type text}}),
    #"Inserted Prefix1" = Table.AddColumn(#"Extracted Last Characters", "Prefix", each "0" & Text.From([Minute], "en-NZ"), type text),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Prefix1",{{"Prefix", "Minute Label"}}),
    #"Extracted Last Characters1" = Table.TransformColumns(#"Renamed Columns3", {{"Minute Label", each Text.End(_, 2), type text}}),
    #"Inserted Prefix2" = Table.AddColumn(#"Extracted Last Characters1", "Prefix", each "0" & Text.From([Second], "en-NZ"), type text),
    #"Renamed Columns4" = Table.RenameColumns(#"Inserted Prefix2",{{"Prefix", "Second Label"}}),
    #"Extracted Last Characters2" = Table.TransformColumns(#"Renamed Columns4", {{"Second Label", each Text.End(_, 2), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Last Characters2", "Index", 0, 1),
    #"Renamed Columns5" = Table.RenameColumns(#"Added Index",{{"Index", "TimeKey"}}),
    #"Inserted Modulo" = Table.AddColumn(#"Renamed Columns5", "Modulo", each Number.Mod([Hour], 12), type number),
    #"Renamed Columns6" = Table.RenameColumns(#"Inserted Modulo",{{"Modulo", "Hour Bin 12"}}),
    #"Inserted Integer-Division" = Table.AddColumn(#"Renamed Columns6", "Integer-Division", each Number.IntegerDivide([Hour], 8), Int64.Type),
    #"Renamed Columns7" = Table.RenameColumns(#"Inserted Integer-Division",{{"Integer-Division", "Hour Bin 8"}}),
    #"Inserted Integer-Division1" = Table.AddColumn(#"Renamed Columns7", "Integer-Division", each Number.IntegerDivide([Hour], 6), Int64.Type),
    #"Renamed Columns8" = Table.RenameColumns(#"Inserted Integer-Division1",{{"Integer-Division", "Hour Bin 6"}}),
    #"Inserted Integer-Division2" = Table.AddColumn(#"Renamed Columns8", "Integer-Division", each Number.IntegerDivide([Hour], 4), Int64.Type),
    #"Renamed Columns9" = Table.RenameColumns(#"Inserted Integer-Division2",{{"Integer-Division", "Hour Bin 4"}}),
    #"Inserted Integer-Division3" = Table.AddColumn(#"Renamed Columns9", "Integer-Division", each Number.IntegerDivide([Hour], 3), Int64.Type),
    #"Renamed Columns10" = Table.RenameColumns(#"Inserted Integer-Division3",{{"Integer-Division", "Hour Bin 3"}}),
    #"Inserted Integer-Division4" = Table.AddColumn(#"Renamed Columns10", "Integer-Division", each Number.IntegerDivide([Hour], 2), Int64.Type),
    #"Renamed Columns11" = Table.RenameColumns(#"Inserted Integer-Division4",{{"Integer-Division", "Hour Bin 2"}}),
    #"Inserted Integer-Division5" = Table.AddColumn(#"Renamed Columns11", "Integer-Division", each Number.IntegerDivide([Minute], 30), Int64.Type),
    #"Multiplied Column" = Table.TransformColumns(#"Inserted Integer-Division5", {{"Integer-Division", each _ * 30, type number}}),
    #"Renamed Columns12" = Table.RenameColumns(#"Multiplied Column",{{"Integer-Division", "Minute Bin 30"}}),
    #"Inserted Integer-Division6" = Table.AddColumn(#"Renamed Columns12", "Integer-Division", each Number.IntegerDivide([Minute], 15), Int64.Type),
    #"Multiplied Column1" = Table.TransformColumns(#"Inserted Integer-Division6", {{"Integer-Division", each _ * 15, type number}}),
    #"Renamed Columns13" = Table.RenameColumns(#"Multiplied Column1",{{"Integer-Division", "Minute Bin 15"}}),
    #"Inserted Integer-Division7" = Table.AddColumn(#"Renamed Columns13", "Integer-Division", each Number.IntegerDivide([Minute], 10), Int64.Type),
    #"Multiplied Column2" = Table.TransformColumns(#"Inserted Integer-Division7", {{"Integer-Division", each _ * 10, type number}}),
    #"Renamed Columns14" = Table.RenameColumns(#"Multiplied Column2",{{"Integer-Division", "Minute Bin 10"}})
in
    #"Renamed Columns14"

Table Calender:

Calender = 
ADDCOLUMNS(
    CALENDAR(DATE(2024,8,1), DATE(2024, 10, 31)),
    "Year", YEAR([Date])
)

Measure:

Max context timestamp = 

    VAR vMaxDate= max(Calender[Date]) 

    VAR vMaxTime = max('Time'[Time])

    RETURN vMaxDate + vMaxTime

Min context timestamp = 

    VAR vMinDate= MIN(Calender[Date]) 

    VAR vMinTime = MIN('Time'[Time])

    RETURN vMinDate + vMinTime

Active in context window = 
// Count of all active machines in time period given by context window.
// Determined using the active-from and active-to, and the given context

 VAR vMaxContextTimestamp = [Max context timestamp]
 VAR vMinContextTimestamp = [Min context timestamp]

VAR vActiveMachines =  
 CALCULATE(
    DISTINCTCOUNT(
        Machines[ID]
    )
    ,

    // condition a: machine starts in period
    (
        Machines[Active-from timestamp] >= vMinContextTimestamp  && 
        Machines[Active-from timestamp] <= vMaxContextTimestamp 
    )

    ||
    // condition b: machine started before period, and has end after start point
    // If 'Active to' is blank, the machine is assumed to operate
    (
        Machines[Active-from timestamp] <= vMinContextTimestamp &&
        (Machines[Active-to timestamp]>= vMinContextTimestamp || ISBLANK(Machines[Active-to timestamp]) )
    )
 )

RETURN 
vActiveMachines +0 // formatting

*Business logic for counting "Active" machines:
A machine is active at a time T if

 -"Active from datetime" is before or on T
- "Active to datetime" is on or after T, or blank (meaning machine is still operating)

A machine is active in time period [t0, t1]
- "Active from datetime" is inside [t0, t1]
- "Active from datetime" is before t0, and t1 is either after t0 or not registered

All help will be much appreciated!

In advance, thank you

[Anonymous]

Thanks for reply, the drafted solution seems promising so far!.

Below I try to clearify your follow up questions:

Time zone and "day": all data same timezone. UTC OK.
By day i mean a particular date (e.g. 2024-08-01)

Datagranularity: at the end is code to genereate data with finer granularity (minute, not hour)
In practice: the data may be rounded/aggregated to for examples 10 minute intervals if that is more convinient.

Machine active in 1 second
Yes - a machine operating in 1 second is active.
In practice, a machine will in most cases operate for at least 10 minutes.
If making things work for 1 second is much harder to solve, we can consider loosen that criteria.

Definition of Active: a machine is active in a timeframe if the active-from and active to is inside that timeframe.

More formally: let t0 and t1 be datetime.
A machine is active in the timeframe [t0,t1] if
- if the timeframe [active from, active to] is inside the timeframe [t0, t1]
(e.g. a machine operating from
[ 2024-08-01 05:10:00  - 2024-08-01 10:12:00 ]
is inside the timeframe
[2024-08-01 00:00:00  - 2024-08-01 23:59:59 ]
and thus active in this timeframe.
But it is outside the timeframe
[2024-08-01 00:00:00  - 2024-08-01 00:06:00 ]
and thus inactive in that timeframe.

Updated data: picture and code to generate table with more granular datetimes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFbDsUgCES3cuN30wCCWrZi3P82ria+iv2dyYFhyNmhuxzGG9JNQPxDUhQFMCqrUFPLlR0ZL6jETqSpwqM4CW+8pBQOoq4VGAQbr47qqQj2HZIGIY14pkfQE1dCdoInEU5iXL6pbcggotmfWioxqRDU4yDS4X3cUdul2dXTPL9fTtAJ/u4KjQlhnc6vCheC717qPJKjrbp4/RDpMKUHc6X8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Active from" = _t, #"Active to" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Active from", type datetime}, {"Active to", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Active from", "Active-from timestamp"}, {"Active to", "Active-to timestamp"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Active-from timestamp", "Active-from timestamp - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Active-from timestamp - Copy", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Active-from timestamp - Copy", "Active-from date"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns1", "Active-to timestamp", "Active-to timestamp - Copy"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Active-to timestamp - Copy", type date}}),
    // Missing active-to-date means machine is still operating (e.g. not stopped)
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"Active-to timestamp - Copy", "Active-to date"}})
in
    #"Renamed Columns2"

[lbendlin]

There is no need for the additional date columns, CALENDAR is taking care of the truncation.

[Anonymous]

Thanks.
This seem promising.
I will test the solution in some days.

And try to make it work when there is a relation between the table Machines and the date table (Calendar).

And see if it can be extended to work when using a Calendar + time table
(e.g. show the number of active machines pr hour)

Do you now is it possible to filter the measure Active Machines a particular date, so that all machines that was active that day is shown?

[lbendlin]

And try to make it work when there is a relation between the table Machines and the date table (Calendar).

These tables need to stay disconnected.  Use CROSSFILTER(,,NONE) if you must.

show the number of active machines pr hour

That will require a more pedestrian approach, without the benefits of CALENDAR.

Do you now is it possible to filter the measure Active Machines a particular date, so that all machines that was active that day is shown?

The current solution already does that?

[lbendlin]

 count the number of active machines for each date 

Define what you mean by "active".  Does a day count when the machine was active for one second on that day?

Speaking of seconds - your sample data only has hour level granularity - is that enough or do you want to provide better sample data?

What does "day" mean for you?  Are these all in the same time zone? Is this in UTC?