cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @tj7933 ,

``````PreviousDayOffHours =
CALCULATE(
SUM(TIMESHEETS_2023[DayOffHours]),
)``````
``````SecondLeaveDayHours =
IF(
[IsLeaveDay] = 1 && [PreviousDayIsLeaveDay] = 1,
[TotalHoursWorked],
BLANK()
)``````

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.

Anonymous
Not applicable

Hi @tj7933 ,

``````PreviousDayOffHours =
CALCULATE(
SUM(TIMESHEETS_2023[DayOffHours]),
)``````
``````SecondLeaveDayHours =
IF(
[IsLeaveDay] = 1 && [PreviousDayIsLeaveDay] = 1,
[TotalHoursWorked],
BLANK()
)``````

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors