Forum Discussion

WHITE655's avatar
WHITE655
Regular Visitor
4 years ago
Solved

Consecutive Days Output

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 -...
General Comment
Need Help
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.