Identifying consecutive rows with criteria

Hi PBOBOP 

 

Your solution is great, Ashish_Mathur and lbendlin. It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.

 

Here is my testing.

 

1. First add indexed columns grouped together in Power Query.

 

Table.Group(Source, {"Item number"}, {{"index", each Table.AddIndexColumn(_, "Index.1", 1, 1, Int64.Type)}})

 

 

2. Expand the table

 

 

I also changed the column names.

 

Here's the complete code in advanced editor.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdM9CoQwEAXgu6QWkpnE/Fxg6+3FwnJhO+8PRuMsuLyZTvKR8ObFLIvjVHNLbnLkmT0HPr/f2767dXoozaKv7fP912oge7o4qkwmJ1EQq+8sNgd8No+RI849lAouZGgzsM/UcCxhxrFCX6melNBDo45FreNWstQKVXzRMfuIE9fcV3reDGsWJUupwp5Fk6nmyUoo+XFgU0ODOu/NPJu7lffwY3xLwvg9XNq0+xcNT10P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item number" = _t, #"Test Date" = _t, Results = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Item number"}, {{"index", each Table.AddIndexColumn(_, "Index.1", 1, 1, Int64.Type)}}),
    #"Expanded index" = Table.ExpandTableColumn(#"Grouped Rows", "index", {"Test Date", "Results", "Index.1"}, {"index.Test Date", "index.Results", "index.Index.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded index",{{"index.Test Date", "Test Date"}, {"index.Results", "Results"}, {"index.Index.1", "Index.1"}})
in
    #"Renamed Columns"

 

 

Close & Apply

 

3. Create two measures as follow

 

rankDate = MAX([Test Date])

rank = IF(MAX([PassResult]) = 1, RANKX(ALLEXCEPT('Table', 'Table'[Item number]), [rankDate], , ASC, Dense), BLANK())

 

 

4. Create a calculated column as follows

 

PassResult = 
VAR _pIndex = [Index.1] - 1
VAR _nIndex = [Index.1] + 1
VAR _lr = CALCULATE(MAX([Results]), FILTER(ALLEXCEPT('Table', 'Table'[Item number]), [Index.1] = _pIndex))
VAR _rl = CALCULATE(MAX([Results]), FILTER(ALLEXCEPT('Table', 'Table'[Item number]), [Index.1] = _nIndex))
VAR _cl = [Results]
VAR _result = IF( _lr = "Pass" && _cl = "Pass" || _cl = "Pass"&& _rl = "Pass", 1, 0)
RETURN
_result

 

 

5. Create two measures as follow

 

Latest Pass Date = 
VAR _maxRank = MAXX(ALLEXCEPT('Table', 'Table'[Item number]), [rank])
VAR _maxDate = CALCULATE(MAX([Test Date]), FILTER('Table', [rank] = _maxRank))
RETURN
_maxDate

 

 

 

Second Latest Pass Date = 
VAR _maxRank = MAXX(ALLEXCEPT('Table', 'Table'[Item number]), [rank])
VAR _maxDate = CALCULATE(MAX([Test Date]), FILTER('Table', [rank] = _maxRank - 1))
RETURN
_maxDate

 

 

Result:

 

I have noticed some discrepancies between your expected results and those labeled in the data sheet. Please feel free to ask me if I have misunderstood.

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.