Forum Discussion

Nomad's avatar
Nomad
Regular Visitor
3 years ago
Solved

Count consecutive value from last date

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 !

Need Help
Tutorial Requests
  • MFelix's avatar
    MFelix
    3 years ago

    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 )
    )

     

     

     

6 Replies

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    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 )
    )