cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MadBern85
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:

MadBern85_0-1648914637390.png

 

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,

Mads

1 ACCEPTED SOLUTION
MadBern85
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()

    )

MadBern85_0-1648988200094.png

 

 

View solution in original post

5 REPLIES 5
MadBern85
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()

    )

MadBern85_0-1648988200094.png

 

 

tomfox
Community Champion
Community Champion

Hi @MadBern85 ,

 

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 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

MadBern85
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,
Mads

hnguy71
Solution Sage
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?



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors