cancel
Showing results for
Did you mean:
Frequent Visitor

Counting consecutive days with gaps

Hi

I've written this measure which counts consecutive days of employee absence:

ConsDaysAbsence =

VAR CurrentDate = MAX(Calendar[Date])

VAR PrevDateNotAbsent = MAXX(FILTER(ALL(Calendar), Calendar [Date] <= CurrentDate && ISBLANK([Absence])), Calendar [Date])

VAR Result = DATEDIFF(PrevDateNotAbsent, CurrentDate, DAY)

RETURN

Result

This works fine and gives the following result:

But in this case the employee had a scheduled day off in between periods of absence. In this case I want to continue the count, so the result in the red box should end with a 5. And it should do the same regardless of the number of days off in between.

Any ideas how to achieve this?

Regards,

1 ACCEPTED SOLUTION
Frequent Visitor

I think maybe I figured it out, but might have to test over time to make sure it's stays consistent.
Here's the measure;

Consecutive Days Absence =

IF([Absence] > 0,

VAR CurrentDate = MAX(Calendar[Date])

VAR PrevDateNotAbsent = MAXX(FILTER(ALL(Calendar), Calendar[Date] <= CurrentDate && ISBLANK([Absence])), Calendar[Date])

VAR PrevDateWithAbsence = MAXX(FILTER(ALL(Calendar), Calendar[Date] < CurrentDate && [Absence] > 0), Calendar[Date])

VAR WorkHoursBetweenAbsence = CALCULATE(SUM(HoursLog[TotalHours]), Calendar[Date] < CurrentDate && Calendar[Date] > PrevDateWithAbsence)

VAR SumConsDaysAbsence = DATEDIFF(PrevDateNotAbsent, CurrentDate, DAY)

VAR PrevSumConsDaysAbsence = CALCULATE(SumConsDaysAbsence, 'Calendar'[Date] = PrevDateWithAbsence)

VAR Result = IF(WorkHoursBetweenAbsence = 0 && [Absence] > 0, PrevSumConsDaysAbsence + 1, SumConsDaysAbsence)

RETURN

Result,

BLANK()

)

5 REPLIES 5
Frequent Visitor

I think maybe I figured it out, but might have to test over time to make sure it's stays consistent.
Here's the measure;

Consecutive Days Absence =

IF([Absence] > 0,

VAR CurrentDate = MAX(Calendar[Date])

VAR PrevDateNotAbsent = MAXX(FILTER(ALL(Calendar), Calendar[Date] <= CurrentDate && ISBLANK([Absence])), Calendar[Date])

VAR PrevDateWithAbsence = MAXX(FILTER(ALL(Calendar), Calendar[Date] < CurrentDate && [Absence] > 0), Calendar[Date])

VAR WorkHoursBetweenAbsence = CALCULATE(SUM(HoursLog[TotalHours]), Calendar[Date] < CurrentDate && Calendar[Date] > PrevDateWithAbsence)

VAR SumConsDaysAbsence = DATEDIFF(PrevDateNotAbsent, CurrentDate, DAY)

VAR PrevSumConsDaysAbsence = CALCULATE(SumConsDaysAbsence, 'Calendar'[Date] = PrevDateWithAbsence)

VAR Result = IF(WorkHoursBetweenAbsence = 0 && [Absence] > 0, PrevSumConsDaysAbsence + 1, SumConsDaysAbsence)

RETURN

Result,

BLANK()

)

Community Champion

How about excluding all days where an employee is having such a scheduled day off when calculating your variable PrevDateNotAbsent?

`"... && ISBLANK([Absence]) && [TotalHours] <> BLANK() ...."`

If this doesn't help, it'd be great if you could share your data 🙂

Frequent Visitor

Hi @tomfox
Thanks so much for the suggestion. I see now that I probably didn't formulate my question well enough.
With your suggestion it continues the count, so that 16.03.2022 return a 2, 17.03.2022 becomes 3 and so on. But what I'm aiming for is for 16.03.2022 to stay at 1 and 17.03.2022 to return 2.

In other words: If current date is a date with absence and all days between current date and previous date with absence is an off day, then the count should continue from the last date with absence.

What data would you need from me? There's quite a lot behind this example.

Regards,

Solution Sage

How can you determine if an employee had a scheduled day off? I don't think you are providing us with enough information. Do you have a sample table?

Frequent Visitor

If "TotalHours" = blank, then it's a scheduled day off.

Announcements