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

tj7933 — Wed, 09 Aug 2023 20:36:50 GMT

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

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

Anonymous — Fri, 11 Aug 2023 06:39:26 GMT

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.

topic Re: Trying to develop a DAX query that will assess if an employee worked on their second day of leave. in DAX Commands and Tips

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

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