Count streak? (consecutive values)

Hey,

as I already mentioned I created the sequence using Power Query, as it performs much better even if it requires some "tweeking" of M code, but nevertheless, it's quite fast.

I started with the transform "Group by" in Power Query:

Not it's necessary to tweek the autogenerated M code, for this switch to the Advanced Editor.
Be aware that the grouping editor can not be opened again, after the M code is adjusted, for this you might want to copy the original line and just comment out the line, use 2 slashes for commenting //
Discover the M function Table.Group and format code similar to this:

#"Grouped Rows" = 
            Table.Group(#"Filtered Rows", {"Team", "Result"}, {{"AllRows", 
            each _, 
            type table [Date=date, Team=text, Result=text, Streak=number, Latest  Streak=text, Latest Streak Count=number]}})

In the second replace the _ with this:

Table.AddIndexColumn( Table.Sort(_, {{"Date", 0} } ) , "rowindexinsubset" ,1,1 )

In the third line remove the record (everything between the square bracketes) and the brackets itself.

After the removal, insert this:

GroupKind.Local

Finally it should look like this:

#"Grouped Rows" = 
            Table.Group(#"Filtered Rows", {"Team", "Result"}, {{"AllRows", 
            each Table.AddIndexColumn( Table.Sort(_, {{"Date", 0} } ) , "rowindexinsubset" ,1,1 ), 
            type table }} , GroupKind.Local)

After this I expanded the table:

After this I created two calculated columns like so:

c latest streak count = 
var _team = 'TeamResults'[Team]
var maxDate = CALCULATE(MAX('TeamResults'[Date]) , FILTER(ALL(TeamResults) , 'TeamResults'[Team] = _team))
var lateststreakcount = LOOKUPVALUE('TeamResults'[rowindexinsubset] , TeamResults[Team] , _team , TeamResults[Date] , maxDate)
return
IF(AND('TeamResults'[Team] = _team , 'TeamResults'[Date] = maxDate)
    , lateststreakcount
    , BLANK()
)

and this

c latest streak = 
var _team = 'TeamResults'[Team]
var maxDate = CALCULATE(MAX('TeamResults'[Date]) , FILTER(ALL(TeamResults) , 'TeamResults'[Team] = _team))
var lateststreak = LOOKUPVALUE('TeamResults'[Result] , TeamResults[Team] , _team , TeamResults[Date] , maxDate)
return
IF(AND('TeamResults'[Team] = _team , 'TeamResults'[Date] = maxDate)
    , lateststreak
    , BLANK()
)

After all this the table will look like this, the result matches your expected results at least as far as I understand the data:

Here you will find a pbix file

Regards,
Tom