Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
JimboSquare
New Member

Calculate time a value is below X in time series data

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:

 

TimestampOutput

 

Cross Flow
09/09/2022 10.00.0150045
09/09/2022 10.00.0237560
09/09/2022 10.00.0351540
09/09/2022 10.00.0450045
09/09/2022 10.00.0535065
09/09/2022 10.00.0645050
09/09/2022 10.05.0630070
09/09/2022 10.10.0635065
09/09/2022 10.15.0751540
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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)

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.