Count streak? (consecutive values)

[thisisausername]

See below my team data:

I want to capture:

The streak - so the count of consecutive Win/Lose/Draw.

The latest streak - what's the current streak?

The latest streak count - the count of the latest streak

Is this possible with DAX?

[TomMartens]

Hey,

this is possible with DAX, but I would recommend to consider using Power Query to create the sequence of consecutive streaks per team.

My assupmtion: there is just one result per team per day?!

Please create a pbix and or xlsx with sample data, upload the files to onedrive or dropbox and share the link.

Regards,

Tom

[thisisausername]

Hi Tom,

Correct - for each team, there is only one result per week commencing date.

XLSX is here: https://www.dropbox.com/s/kaqy9uy1s2ytd05/TeamResults.xlsx?dl=0

Thank you

[TomMartens]

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

[DSharbaugh]

Hi Tom,  

I am trying to follow these instructions for a similar need, but at the point of creating the two calculated fields I get the following error: "A table of multiple values was suppied where a single value was expected".  Are you able to suggest any corrections?