Counting number of consecutive days

[JonSwed]

Hi all,

I have a chat function where I want to count the number of consective days that pairs were chatting.

My data looks something like this:

and the desired output would be like this:

Where I show the maximum number of days where the pairs of users consecutively messaged each other.

Any help gratefully received, thanks.

[JonSwed]

Thanks for the input here. Currentlly testing the different solutions. Again, many thanks.

[V-lianl-msft]

Hi @JonSwed ,

Based on your description, you want to figure out how many days send messages to each other.

You can try to do this by creating a calculated column to get the number of messages sent in the next day, and then use the current day and the next day as filter to determine whether sending messages continuously.

nextday = CALCULATE(LASTNONBLANK('Table (2)'[Total Messages],1),
FILTER('Table (2)',
EARLIER('Table (2)'[Date])='Table (2)'[Date]-1
&&'Table (2)'[Name]=EARLIER('Table (2)'[Name])))

Measure = COUNTROWS(FILTER('Table (2)','Table (2)'[Total Messages]>0&&'Table (2)'[nextday]>0))+1

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

[camargos88]

@FrankAT ,

This thread was opened by @JonSwed . Check the first message.

[FrankAT]

Hi @camargos88 

which sequence would you prefer, the first, the second, the max consecutive sequence, the amount of tied consecutive sequences or the sum of them?

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[FrankAT]

Hi @camargos88 

I think you can do it like this:

Number of Consecutive Days = 
CALCULATE(
    COUNTROWS('Data Table'),
    FILTER(
        'Data Table',
        'Data Table'[Total messages] > 0
    )
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[camargos88]

@FrankAT ,

This doesn't guarantee the date's sequence. 

[camargos88]

@JonSwed ,

Try this measure:

_Consecutive Days = 
VAR _tb = 
    SUMMARIZE(
        'Table', 
         'Table'[User],
        'Table'[Date], 
        "Consecutive", IF('Table'[Date] - CALCULATE(MAX('Table'[Date]), FILTER(ALL('Table'[Date]), 'Table'[Date] < EARLIER('Table'[Date]))), 1, 0),
        "TotalMessages", SUM('Table'[Total Messages])
        )
RETURN COUNTX(FILTER(_tb, ([Consecutive] = 1 || [Consecutive] = BLANK()) && [TotalMessages] > 0), [TotalMessages])