Count consecutive value from last date

[Nomad]

Hi everyone,

Not sure how to approch this in powerbi.

I have a table with people and all there vacation, sick days, formation, etc..

Let say i want to count/sum (?) all the time a person was in sick leave, i can see how to count all value, but i would only want the last consecutive date that person was in sick leave, for the exemple : 

Adel was on sickleave for 4 consecutive days from 16/02/2023 - 19/02/2023 and also for 3 consecutive days from 21/03/2023 - 23/03/2023.

But i only have an interset to see last group of days she was on sick leave.

The end result should be something like that :

Another exemple for Bastien :

He was in sick leave several time but i only need too see when was the last time and for how long.

Im hope im clear enough in the exemple.

I attach the test model for some clarity.

https://drive.google.com/file/d/1XxzyYgdei3UuisDEqjPZ8aaJzKHFOpen/view?usp=drive_link

Thank you for your support !

[MFelix · Accepted solution]

Hi @Nomad ,

Try the following:

• Add a column with the following code:

Consecutive Days = 
VAR temptable =
    FILTER (
        ESTIMATED_DAILY_ABSENCE,
        ESTIMATED_DAILY_ABSENCE[DATE]< EARLIER ( ESTIMATED_DAILY_ABSENCE[DATE] )
            && ESTIMATED_DAILY_ABSENCE[FIRST_NAME]
                = EARLIER ( ESTIMATED_DAILY_ABSENCE[FIRST_NAME] )
    )
RETURN
    DATEDIFF (
        MAXX ( temptable, ESTIMATED_DAILY_ABSENCE[DATE] ),
        ESTIMATED_DAILY_ABSENCE[DATE],
        DAY
    )

This will return the number of days from last sick leave

Now add this measures:

last day of sick =
VAR temptable =
    FILTER (
        ESTIMATED_DAILY_ABSENCE,
        ESTIMATED_DAILY_ABSENCE[Consecutive Days] > 1
    )
VAR datetop =
    MAXX ( temptable, ESTIMATED_DAILY_ABSENCE[DATE] )
RETURN
    MAXX (
        FILTER ( ESTIMATED_DAILY_ABSENCE, ESTIMATED_DAILY_ABSENCE[DATE] >= datetop ),
        ESTIMATED_DAILY_ABSENCE[DATE]
    )


Number of days =
VAR temptable =
    FILTER (
        ESTIMATED_DAILY_ABSENCE,
        ESTIMATED_DAILY_ABSENCE[Consecutive Days] > 1
    )
VAR datetop =
    MAXX ( temptable, ESTIMATED_DAILY_ABSENCE[DATE] )
RETURN
    COUNTROWS (
        FILTER ( ESTIMATED_DAILY_ABSENCE, ESTIMATED_DAILY_ABSENCE[DATE] >= datetop )
    )

[Nomad]

Hi, im going to go with that.

@MFelix and @danextian thank youu both for your time!

[danextian]

Hi @Nomad ,

Please make your link public.

[Nomad]

oh right, my bad, done!

[danextian]

So I took the liberty to just based the proposed solution on your first screenshot.  This makes use of several helper columns and scanning of the table several times.  Please see attached pbix for the details.

[Nomad]

Amazing, thanks!

There is also a way to only display the last "latest sick leave", and not there previuous one ?