Count last(actual) consecutive sorted rows

[cullo6]

Hi, I want count last(actual) consecutive rows with text Win or Loss(It matter on latest row) ,sorted by Date with time and by Name like in table below. But if will be measure, it is not necessary to sort by Name, I will use it in Legend. It can be 2 measures, like MaxConLoss and MaxConWin or columns. Can you help me? Thanks.

[cullo6]

It´s possible? Help me, please.

[v-sihou-msft · Accepted solution]

@cullo6

In this scenario, you can create a WinValidation column to tag "1" for each win game. Check the if there is no win game before current row, you just need to sum the WinValidation up to current date. Otherwise you should calculate the max date for the last loss game, then calculate total WinValidation from that date to current date. Please refer to my sample below:

1. Add a WinValidation column.

WinValidation = IF(Table1[Result]="Win",1,0) 

2. Create a calculated column for Consecutive Win:

Consecutive Win = 
SWITCH (
    TRUE (),
    Table1[WinValidation] = 0, 0,
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALL ( Table1 ),
            Table1[WinValidation] = 0
                && Table1[Name] = EARLIER ( Table1[Name] )
                && Table1[Date] <EARLIER ( Table1[Date] )
        )
    )
        = 0, CALCULATE (
        SUM ( Table1[WinValidation] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Name] = EARLIER ( Table1[Name] )
                && Table1[Date] <= EARLIER ( Table1[Date] )
        )
    ),
    CALCULATE (
        SUM ( Table1[WinValidation] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Name] = EARLIER ( Table1[Name] )
                && Table1[Date]
                    > CALCULATE (
                        MAX ( Table1[Date] ),
                        FILTER (
                            ALL ( Table1 ),
                            Table1[WinValidation] = 0
                                && Table1[Name] = EARLIEST ( Table1[Name] )
                                && Table1[Date] < EARLIEST ( Table1[Date] )
                        )
                    )
                && Table1[Date] <= EARLIER ( Table1[Date] )
        )
    )
)

Regards,

[Shales]

Thank you so much for this. This is exactly what I needed in my report. Perfect! 😀

[cullo6]

@v-sihou-msft

Thank you very much for your reply, good job. I just have one thing. I wanted only the current consecutive rows, so in your table it was for "a" 1 (1 win) and for "b" 2 (2 Loss). Loss rows I want count too...it matter on last row. My idea is, doing it like you do, count current consecutive rows, but so if next row will be lesser or equal than previous row, value will be 0, and next rows will be 0 too, but only for this name. Thank you.

[v-sihou-msft]

@cullo6

For calculating Consecutive Loss, you just need to same thing as calculating Consecutive Win. Just Create another LossValidation first. then create another same calculated column, and replace the WinValidation with LoassValidation. 

We can't combine the Consecutive Win and Loss in same column. Because it's not possible the to have one row directly compare with previous row or next row in DAX. And we have to tag Loss into "0" so that we can cumulative sum the WinValidation to get the Consecutive Wins. 

Regards,