Trying to develop a DAX query that will assess if an employee worked on their second day of leave.

[tj7933]

Ok,

I must calculate the hours worked on an employee's second leave day.

I cannot figure out how to work out whether or not the previous day was a day of leave.  I think I am missing something simple.

Here is my logic and my attempts to solve this problem:

1)Did the employee work today?  (I have covered this)  - Power Query - Total Hours Worked
2) Is today a day of leave? (I have covered this) - Dax calculated Column = =IF([Day Name]="Sunday" || [Day Name]="Saturday" || [STATS]="STAT",1,0)

3) Did the employee work the previous day? (I have covered this) DAX Measure =CALCULATE(DISTINCTCOUNT(TIMESHEETS_2023[START - Date]), PREVIOUSDAY('Calendar'[Date]))

4) Is the previous day a day of leave (Saturday, Sunday or STAT)?  (not sure how to address this) this is my attempt DAX Measure =CALCULATE(SUM(TIMESHEETS_2023[DayOffHours]),DATEADD('Calendar'[Date],-1,DAY)) (DAY OFF HOURS = Calculated Column = IF([DayOff]>0,[TotalHoursWorked],0)
5) if all items 1 to 5 are yes, sum the total hours worked today. (not sure how to address this)

If you think you can do this more simply (I am overthinking things) and/or you have ideas to address this calculation, I am very open to learning more and hearing suggestions.

Thank you
Tammy

[v-rongtiep-msft · Accepted solution]

Hi @tj7933 ,

Please have a try.

PreviousDayOffHours =
CALCULATE(
    SUM(TIMESHEETS_2023[DayOffHours]),
    DATEADD('Calendar'[Date], -1, DAY)
)

SecondLeaveDayHours =
IF(
    [IsLeaveDay] = 1 && [PreviousDayIsLeaveDay] = 1,
    [TotalHoursWorked],
    BLANK()
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.