Build measure or column to show a streak by consecutive dates a person meets a request

[Anonymous]

I have a table with Names and Dates. This table shows me that a person completed the request total for a given day. I'd like to create a measure to count the streak of days they meet requests.

For example: below shows two people - Berry and Bob.

Berry had a streak from 2019-12-10 to 2019-12-13 so I'd like to see the streak column count 1,2,3,4 and then because there is no record for Berry on 2019-12-14 the streak ends. A new streak would begin on the date that Berry has a record (in this case 2019-12-16).

Here is the expected result that I just created in Excel - streak of days in a row:

Any help on this would be really appreciated. This one has me stumped.

Thanks, Ben

[andre]

here is a video on how to calculate a streak without having to do anything wiht calculated columns.. all of the logic is in the measure itself:

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

I have created a sample for your reference, please check the following steps as below.

1. Insert an index column in power query.

2. Then we can create the calculated columns to work on it.

Column = 
VAR ind = 'Table'[Index] - 1
VAR lastd =
    CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[Index] = ind ) )
VAR ld = 'Table'[Date] - 1
RETURN
    IF ( lastd = ld, 1, BLANK () )

Column 2 = 
var ind = 'Table'[Index]
var lastindex = CALCULATE(MAX('Table'[Index]),FILTER('Table','Table'[Column]=BLANK() && 'Table'[Index]<ind))
return
IF('Table'[Column]<>BLANK(),lastindex,'Table'[Index])

countrows = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Name] = EARLIER ( 'Table'[Name] )
            && 'Table'[Column 2] = EARLIER ( 'Table'[Column 2] )
            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
    )
)

Pbix as attached.

[Greg_Deckler]

You might be able to adapt Cthulhu for this purpose:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739?search-action-id=98319201...