Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate time difference between specific rows

Hello Power BI Community,

 

I was hoping you may offer some help to a novice Power BI User.

 

I need to calculate how long a pump has been running for. If 'Pump1 RN' = 1 that means it is running, if 'Pump1 RN' = 0 it means it isn't running. 

 

How do I work out the total time that the pump has been running?

 

 

 

  • Jos_Woolley's avatar
    Jos_Woolley
    5 years ago

    Ok, thanks. Try this measure:

     

    MyMeasure =
VAR MyTable =
    ADDCOLUMNS (
        'Table',
        "Previous Date.Time",
            CALCULATE (
                MAX ( 'Table'[Date.Time] ),
                FILTER ( 'Table', 'Table'[Date.Time] < EARLIER ( 'Table'[Date.Time] ) )
            ),
        "Previous Pump 1 RN",
            LOOKUPVALUE (
                'Table'[Pump 1 RN],
                'Table'[Date.Time],
                    CALCULATE (
                        MAX ( 'Table'[Date.Time] ),
                        FILTER ( 'Table', 'Table'[Date.Time] < EARLIER ( 'Table'[Date.Time] ) )
                    )
            )
    )
RETURN
    SUMX (
        MyTable,
        IF ( [Previous Pump 1 RN] = 1, [Date.Time] - [Previous Date.Time] )
    )

     

    though this will return 14:00 for the data you give, not 13:57. I personally think this makes more sense (i.e. assuming the pump runs right up until the next 0); returning 13:57 would be a bit trickier.

    This measure will respond to filters on the Date.Time field, e.g. filtering between 01/04/2020 04:00 and 01/04/2020 11:00 will produce a result of 07:00.

    Regards