Consecutive Dates with data

[karo]

Hi,

I need to calculate consecutive days of Sickness on Employee level, list Employees with more than 7 days, 15 days, 30 days and 3 months of consecutive sickness. And moreover, allow to filter those groups based on other fields (e.g. Department, Function, Possition, Location).

Can anyone help?

Can you share any ideas, best practises, tutorials, please? 

[karo]

@v-easonf-msft @amitchandak Thank you so much 😍 ! I will combine your ideas to deal with my real data. 

[v-easonf-msft · Accepted solution]

Hi, @karo 

Based on your scenario, I made a simple example to count consecutive absence days.You can further filter the data based on the newly fields 'Max of Consecutive absent' .

Related codes of calculated columns:

flag = IF('Table1'[Statue]="absent",1,0) 

Consecutive absent =
SWITCH (
    TRUE (),
    Table1[flag] = 0, 0,
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALL ( Table1 ),
            Table1[flag] = 0
                && Table1[Name] = EARLIER ( Table1[Name] )
                && Table1[Date] < EARLIER ( Table1[Date] )
        )
    ) = 0,
        CALCULATE (
            SUM ( Table1[flag] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Name] = EARLIER ( Table1[Name] )
                    && Table1[Date] <= EARLIER ( Table1[Date] )
            )
        ),
    CALCULATE (
        SUM ( Table1[flag] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Name] = EARLIER ( Table1[Name] )
                && Table1[Date]
                    > CALCULATE (
                        MAX ( Table1[Date] ),
                        FILTER (
                            ALL ( Table1 ),
                            Table1[flag] = 0
                                && Table1[Name] = EARLIEST ( Table1[Name] )
                                && Table1[Date] < EARLIEST ( Table1[Date] )
                        )
                    )
                && Table1[Date] <= EARLIER ( Table1[Date] )
        )
    )
)

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@karo , refer if these can help

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

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

https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Continuous-Streak-One-Day-Differenc...

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