Counting Weekends Where Friday and Monday Are Sick Days
Hi I'm currently working on a formula to count sick days for employees, specifically considering weekends where both Friday and Monday are marked as sick days. However, I'm encountering some issues with the formula, and I'd greatly appreciate your expertise and insights. The purpose of the formula is to: Count sick days when an employee is absent for the entire day or more. Accurately identify consecutive weekends when both Friday and Monday are sick days and add them to the count. Unfortunately, the current formula isn't functioning as expected, and the weekends aren't being counted correctly. I've tried debugging the formula and making adjustments, but I'm still facing challenges. Please help me figure oth how to counts the weekends prpoerly! SickDays = VAR EmpNo = F_Illness[Emp No] VAR IllnessHours = F_Illness[Illness Hours] VAR CurrentDate = RELATED(D_date[Date]) VAR DayOfWeek = WEEKDAY(CurrentDate, 2) -- 1 = Monday, 2 = Tuesday, ..., 7 = Sunday VAR WorkHoursOnCurrentDay = CALCULATE( MAX(D_WorkSchedule[Hours]), D_WorkSchedule[Day] = SWITCH( DayOfWeek, 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday", 7, "Sunday" ) ) VAR NextDate = CurrentDate + 1 VAR PreviousDate = CurrentDate - 1 // Check if the previous day is Saturday and the day before is Sunday (consecutive weekends) VAR IsPreviousConsecutiveWeekend = IF( DayOfWeek = 7 && WEEKDAY(PreviousDate, 2) = 6, -- Sunday and Saturday CALCULATE( SUM(F_Illness[Illness Hours]), F_Illness[Emp No] = EmpNo, F_Illness[Date] = PreviousDate ) >= WorkHoursOnCurrentDay, -- Full sick day on Friday FALSE() ) // Check if the next day is Sunday and the day after is Monday (consecutive weekends) VAR IsConsecutiveWeekend = IF( DayOfWeek = 6 && WEEKDAY(NextDate, 2) = 7, -- Saturday and Sunday CALCULATE( SUM(F_Illness[Illness Hours]), F_Illness[Emp No] = EmpNo, F_Illness[Date] = NextDate ) >= WorkHoursOnCurrentDay, -- Full sick day on Monday FALSE() ) RETURN IF( IllnessHours >= WorkHoursOnCurrentDay || IsConsecutiveWeekend || IsPreviousConsecutiveWeekend || (IsConsecutiveWeekend && IsPreviousConsecutiveWeekend), -- Check for both consecutive weekends 1, 0 )
Help Needed: DAX Measure to determine if SLA reached the target or not.
Hi Everyone, I'd like to ask for your help. I have data with different targets per SLA. Minimum column is also target but it's the minimum % an SLA must have. I created a measure that I will use to create an icon (using conditional format) to determine if the SLA Summary results met the target or not. _ConditionSLATarget = MAXX('Monthly Summary', IF('Monthly Summary'[_SLA Summary Results] >= 'Monthly Summary'[Target],1, IF('Monthly Summary'[_SLA Summary Results] >= 'Monthly Summary'[Minimum],0, IF('Monthly Summary'[_SLA Summary Results] < 'Monthly Summary'[Minimum],-1)))) ----------------------------------------------------------------------------------------------------- My Table is: 'Monthly Summary' SLA measure is _SLA Summary Results DAX Formula for _SLA Summary Results below: _SLA Summary Results = SUM('Monthly Summary'[Met])/SUM('Monthly Summary'[Volume]) However, there's something strange when I put it in a table. If you notice, DAT 1 SLA should be "-1" with a downward arrow because SLA Summary result of 85.32% did not meet the minimum target of 96% I don't know what went wrong so I'd like to ask for your expertise on this matter. Let me know if you need additional info. Thank you Here's a screenshot of my conditional format for the icons in case you need.
