Max of a multiple ranges in a table

[PaulHallam]

Hi,

I have a subset of sensor data as below (link at bottom of question).

Date                 SensorNo  Time          Hits

13/06/2019	12214	07:00:00	0
13/06/2019	12213	07:00:00	0
13/06/2019	12212	07:00:00	0
13/06/2019	12211	07:00:00	0
13/06/2019	12210	07:00:00	0
13/06/2019	12214	07:10:00	0
13/06/2019	12213	07:10:00	0
13/06/2019	12212	07:10:00	0
13/06/2019	12211	07:10:00	0

I need to find the maximum number of hits in any 30 minute period in a day.

Each time includes the measurement of 10 minutes after so 07:00 is in effect data from 07:00 to 07:10

So essentially I need the maximum number from;

Number of sensor hits at 07:00:00, 07:10:00 and 07:20:00 on 13th June

Number of sensor hits at 07:10:00, 07:20:00 and 07:30:00 on 13th June

Number of sensor hits at 07:20:00, 07:30:00 and 07:40:00 on 13th June

etc…..

Number of sensor hits at 07:00:00, 07:10:00 and 07:20:00 on 14th June

Number of sensor hits at 07:10:00, 07:20:00 and 07:30:00 on 14th June

Number of sensor hits at 07:20:00, 07:30:00 and 07:40:00 on 14th June

etc.

The value must be correct when changing dates by slicer,

Way above my level at this stage of my PowerBi journey

https://1drv.ms/x/s!AgQfcEtrCvUozG1XTbLKW6GoDeS6

Thanks

[v-yuta-msft]

@PaulHallam ,

"

Number of sensor hits at 07:00:00, 07:10:00 and 07:20:00 on 13th June

Number of sensor hits at 07:10:00, 07:20:00 and 07:30:00 on 13th June

Number of sensor hits at 07:20:00, 07:30:00 and 07:40:00 on 13th June

etc…..

Number of sensor hits at 07:00:00, 07:10:00 and 07:20:00 on 14th June

Number of sensor hits at 07:10:00, 07:20:00 and 07:30:00 on 14th June

Number of sensor hits at 07:20:00, 07:30:00 and 07:40:00 on 14th June

etc.

"

Do you want to achieve the max hit number during 07:00:00 to 07:30:00, 07:10:00 to 07:40:00, 07:20:00 to 07:50:00, as you said every 30min in each day? Or you just need to achieve the max hit number at each time above? I'm not very clear about your requirement, if possible could you show the expected result?

Regards,

Jimmy Tao

[PaulHallam]

Hi Jimmy,

I want to know the maximum number of sensor hits in ANY 30 minute period on ANY day.

The time value 07:00 is actually the start of a 10 minute period and the value is if there was a hit (1) or not (0) in that 10 minute period. So the 07:00 is for the period 07:00 to 07:10.

For the actual time range 09:40 to 10:10 i would need to analyse 09:40, 09:50 and 10:00.

Hope this makes sense. I have updated the spreadsheet to show what i am trying to achieve.

Paul

[PaulHallam]

Any Luck