Forum Discussion

karo's avatar
karo
Advocate V
4 years ago
Solved

Consecutive Dates with data

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 ...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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.