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

7 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    5 years ago

    Hi,

    Actually that result is perfectly correct for the filtering you've applied. A filter such as the one you've applied does not automatically include the entries in between the ones you've selected - it simply filters for those two dates.

    I'll let you play about with other filter visuals and options (for example, sliders) until you've found the right one for you.

    Regards

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    5 years ago

    Hi,

    If the pump was running at 06:00 but not at 07:00, what is the assumption about the time at which it stopped running? 06:01? 06:59?

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello, it would be 06:59

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    5 years ago

    Then since your Date.Time intervals are consistenly of length 1 hour, you can just sum the Pump 1 RN column. If in reality the Date.Time intervals are not as you posted, please post a more realistic example. Also, perhaps confirm your expected result for the sample data you posted.

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Jos, yes sorry I understand what you mean. I have updated the post with a more realistic example of the data. The pump is/isn't running until the next time stamp. For example, the pump has ran for a total of 13hrs and 57 minutes from 01/04/2020 00:00:00 to 01/05/2020 02:30:00

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Jos, thanks so much for taking the time to try and work on this. Unfortunately it doesn't seem to work, I have tried on my data and the sample data which I provided. For example, if I have no filters selected it will show 14:00 as you stated, however if I put a filter in (as per the below) it then shows 18:00 which isn't correct. I have it in the format of (hh:mm)