topic Re: Calculate time difference between specific rows in DAX Commands and Tips

Calculate time difference between specific rows

Anonymous — Fri, 27 Aug 2021 12:36:35 GMT

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?

Re: Calculate time difference between specific rows

Jos_Woolley — Fri, 27 Aug 2021 12:11:16 GMT

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

Re: Calculate time difference between specific rows

Anonymous — Fri, 27 Aug 2021 12:24:42 GMT

Hello, it would be 06:59

Re: Calculate time difference between specific rows

Jos_Woolley — Fri, 27 Aug 2021 12:34:01 GMT

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

Re: Calculate time difference between specific rows

Anonymous — Fri, 27 Aug 2021 12:42:30 GMT

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

Re: Calculate time difference between specific rows

Jos_Woolley — Sat, 28 Aug 2021 06:32:46 GMT

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

Re: Calculate time difference between specific rows

Anonymous — Wed, 01 Sep 2021 10:02:32 GMT

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)

Re: Calculate time difference between specific rows

Jos_Woolley — Wed, 01 Sep 2021 11:31:27 GMT

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