Calculate time a value is below X in time series data

[JimboSquare]

Hi All, 

I have a table of data built from a folder of CSV files containing time series data - the data can be per second or per 5 minutes.

Each row has a timestamp and several columns of data.

I am trying to find the total amount of time a value spent below X. This would be much simpler if the logged data was per second, but as it can be per 5 mins as well it makes it slightly harder.
I guess I need to find the start timestamp the value drops below the threshold and the timestamp it rises above, find the datediff between the star and finish, then add up all the occurrences throughout the data.

Taking the data table below, I want to return the total time the Output was logged below 400, the answer to the below would be 10 mins 2 seconds:

Timestamp	Output	Cross Flow
09/09/2022 10.00.01	500	45
09/09/2022 10.00.02	375	60
09/09/2022 10.00.03	515	40
09/09/2022 10.00.04	500	45
09/09/2022 10.00.05	350	65
09/09/2022 10.00.06	450	50
09/09/2022 10.05.06	300	70
09/09/2022 10.10.06	350	65
09/09/2022 10.15.07	515	40

[lbendlin · Accepted solution]

oooh, Nyquist and Shannon will not be happy about this...

Anyway, do you need the solution in DAX or in Power Query? In DAX it would look something like 

Measure_t =
VAR a =
    ADDCOLUMNS(
        SELECTCOLUMNS(
            'Table',
            "Timestamp", 'Table'[Timestamp],
            "Output", 'Table'[Output]
        ),
        "PT",
            VAR t = [Timestamp]
            RETURN
                MAXX( FILTER( 'Table', 'Table'[Timestamp] < t ), 'Table'[Timestamp] )
    )
VAR b =
    ADDCOLUMNS(
        a,
        "PV",
            VAR p = [PT]
            RETURN
                MAXX( FILTER( 'Table', 'Table'[Timestamp] = p ), 'Table'[Output] )
    )
RETURN
    SUMX( FILTER( b, COALESCE( [PV], 400 ) < 400 ), [Timestamp] - [PT] )

By the way the actual result  is 10 minutes 3 seconds (not 10 minutes 2 seconds)