Return the next 20 events after one selected event

[rebrow31]

Hi, 

I have a list of downtime events of several machines. Some downtime events are planned and some are unplanned. I want to be able to select a specific planned downtime event, and then to see the next 20 downtime events that occured after the specific planned downtime event that I selected. How can I do this? 

[Dangar332 · Accepted solution]

Hi, @rebrow31 

Try below measure 

result = 
var a =CALCULATE(
    MIN('Table 2'[Value]),
    INDEX(
        20,
        FILTER(ALL('Table 2'[Machine],'Table 2'[Value]),'Table 2'[Value]>=MIN(Datetable[Date])),
        ORDERBY('Table 2'[Value],ASC),,
        PARTITIONBY('Table 2'[Machine]),
        MATCHBY('Table 2'[Machine],'Table 2'[Value])
    ),
    KEEPFILTERS(Datetable[Date])
)
var b= MIN('Table 2'[Value])+DATEDIFF(MIN(Datetable[Date]),a,DAY)
var c = CALCULATE(MAX('Table 2'[Value]),ALL('Table 2'[Value]))
var d= IF(ISBLANK(a),c,a)
var e= IF(ISFILTERED('Table 2'[Value]) && MIN('Table 2'[Value])>=SELECTEDVALUE(Datetable[Date]) && MIN('Table 2'[Value])<=d,1,0)
RETURN
e

i am taking data of machine and date 

consider table2[value] as your downtime date column

if after selected date you have less then 20 downtime field it also show that 
see below image

use measure in visual level filter

you can download file from below

[johnbasha33]

@rebrow31 

You can achieve this by following these steps:

1. Sort Downtime Events: First, sort your list of downtime events chronologically based on their timestamps.

2. Identify Planned Downtime Event: Locate the specific planned downtime event that you've selected in the sorted list.

3. Find Next 20 Downtime Events: Once you've located the selected planned downtime event, iterate through the sorted list to find the next 20 downtime events that occurred after the selected event.

[rebrow31]

Hi, 

Yes I can easily do this but it's a long winded way as there are so many downtime events. I want to be able to Pareto chart the next 20 downtime events after a selected stoppage and going through a massive list of downtime events doesn't work great.