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 those groups based on other fields (e.g. Department, Function, Possition, Location).

 

Can anyone help?

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

 

 

 

 

 

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.

3 Replies