Consecutive Days Output

[WHITE655]

I am not sure where to start on this. 

I am wanting to have an output that will take the consecutive days down and total them up with the number of days "down". 

Attached the my sample data - The output wants is what I am wanting to calculate. 

[v-zhangti · Accepted solution]

Hi, @WHITE655 

You can try the following methods.

Column:

Judge = 
DATEDIFF([Date]-2,MaxX(FILTER('Table',[Date]=EARLIER('Table'[Date])-1),[Date]),DAY)

First day = 
IF([Judge]<>BLANK(),0,1)

Consecutive days =
VAR last_1_date =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            'Table',
            'Table'[date] <= EARLIER ( 'Table'[date] )
                && [First day] = 1
        )
    )
VAR sum_ =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[date] >= last_1_date
                && 'Table'[date] <= EARLIER ( 'Table'[date] )
        )
    )
RETURN
    IF ( [First day] = 1, 1, sum_ )

Output =
VAR _N1 =
    CALCULATE (
        MIN ( 'Table'[date] ),
        FILTER ( 'Table', 'Table'[date] > EARLIER ( 'Table'[date] ) && [First day] = 1 )
    )
VAR _N2 =
    CALCULATE (
        MAX ( 'Table'[Consecutive days] ),
        FILTER ( 'Table', [Date] >= EARLIER ( 'Table'[Date] ) && [Date] <= _N1 )
    )
VAR _N3 =
    CALCULATE (
        MAX ( 'Table'[Consecutive days] ),
        FILTER ( 'Table', [Date] = MAX ( 'Table'[Date] ) )
    )
RETURN
    IF (
        [First day] = 1
            && _N1 <> BLANK (),
        _N2,
        IF ( [First day] = 1 && _N1 = BLANK (), _N3 )
    )

Is this the result you expect? Please refer to the attachment for details.

Best Regards,

Community Support Team _Charlotte

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

[amitchandak]

@WHITE655 , to me, seems like a Continuous Streak problem

refer

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...

Continuous streak : https://youtu.be/GdMcwvdwr0o

https://community.powerbi.com/t5/Desktop/Need-help-in-DAX/m-p/1277302#M559393