creating a datetime-table with running and not running between start-and stoptimes

[Anonymous]

Hi, 

I am currently trying to create a status-table for our machines. I want to create a table that has;

- a row for each minute

- a date/time-column (showing each minute)

- a column where the status is shown as either on or off, depending if it is between given start- and stoptimes from a runs table.

(Since this report will be limited to a maximum of one week it should not become too large since there is only 10800 minutes in a week).

I currently have this data as an example:

Let's say I want to fill the date/time-table mentioned before with "on" between a start and stop, and "off" outside of these times. How would I go about this? It can be either in DAX or the power query. 

[tamerj1]

Hi @Anonymous 
The one week will require 345,600 rows. Are you ok with that?

[tamerj1]

@Anonymous 
Let me propose this any way. See attached file

Status = 
MAXX ( 
    CROSSJOIN ( 
        VALUES ( 'Date'[Date] ),
        VALUES ( 'Time'[Time] )
    ),
    IF ( 
        ISEMPTY ( 
            FILTER (
                'Table',
                'Table'[Start] <= 'Date'[Date] + 'Time'[Time]
                    && 'Table'[Stop] >= 'Date'[Date] + 'Time'[Time]
            )
        ),
        "OFF",
        "ON"
    )
)

[Anonymous]

Thank you!

It will always be necessary to split the dates and times; there is no way to compare them directly? I already have a date/time table in the document with the date/time-values over a set week (Which is given from a parameter in powerquery). 

[tamerj1 · Accepted solution]

@Anonymous 

Yes of course you can compare directly. The only concern is when you have more dates as one year will require tens of milions of rows. One week is totally fine.

Status =
MAXX (
    VALUES ( DateTime[DateTime] ),
    IF (
        ISEMPTY (
            FILTER (
                'Table',
                'Table'[Start] <= DateTime[DateTime]
                    && 'Table'[Stop] >= DateTime[DateTime]
            )
        ),
        "OFF",
        "ON"
    )
)