Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Mads
Solved! Go to Solution.
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()
)
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()
)
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! |
#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
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?
If "TotalHours" = blank, then it's a scheduled day off.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |