Count consecutive value from last date

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