Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

 

tackytechtom
Super User
Super User

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Hi @tackytechtom 
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
Memorable Member
Memorable Member

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.