cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
Community Support

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.

Community Support

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors