count number of consecutive days

[Anonymous]

Hi, 

I would like to have the count of consecutive days in a calculated column based on for example sickday.

eks
employee   date           Sick =1     consecutive days
A               1.1.2020       1                2 (1.1+2.1)

B                1.1.2020      1                 3 (1.1+2.1+3.1)
C                1.1.2020      0                 0
A                2.1.2020       1                2 (1.1+2.1)
B                2.1.2020       1                 3 (1.1+2.1+3.1)

C                2.1.2020        1                2 (2.1+3.1)

A                3.1.2020        0                0

B                 3.1.2020       1                3 (1.1+2.1+3.1)
C                 3.1. 2020      1                 2 (2.1+3.1)

Any help would be grateful!

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

You could use this formula to get it:

Result = 
VAR _date1 =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            CALCULATETABLE ( 'Table',ALLEXCEPT('Table','Table'[employee] ),'Table'[Sick]=0),
            'Table'[date] <= EARLIER (  'Table'[date] )
        )
    )
VAR _date2 =
    CALCULATE (
        MIN(  'Table'[date] ),
        FILTER (
            CALCULATETABLE ( 'Table',ALLEXCEPT('Table','Table'[employee] ),'Table'[Sick]=0),
            'Table'[date] > EARLIER (  'Table'[date] )
        )
    )
VAR _date3=IF( ISBLANK(_date2),CALCULATE (
        MAX ( 'Table'[date] ),ALLEXCEPT('Table','Table'[employee] ))+1,_date2)

RETURN
var a=    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
           ALLEXCEPT( 'Table','Table'[employee]),
             'Table'[date] <_date3&& 
                'Table'[date] > _date1
        )
    ) + 0

return
 if('Table'[Sick]=0,0,a)

Regards,

Lin

[Watsky]

This solved my problem! Thank you!

[Anonymous]

Brilliant!!!!!

[ryan_mayu]

@Anonymous 

you can try to create a column

Column = if('Table'[Sick]=0,0,sumx(FILTER('Table','Table'[employee]=EARLIER('Table'[employee])),'Table'[Sick]))

[Anonymous]

Tnx for your reply, however it does not give the correct numbers.

[amitchandak]

@Anonymous , refer if these can help

https://community.powerbi.com/t5/Desktop/Build-measure-or-column-to-show-a-streak-by-consecutive-dates-a/td-p/899915

https://community.powerbi.com/t5/Desktop/Win-Losing-Streak/td-p/273547