Consecutive Days worked in 7 and 14 days based on a date slicer with days worked gaps

6701Beckster — Tue, 24 Jan 2023 16:41:52 GMT

I have a tricky situation that I'm looking for help with. Working on a calculation (could be more than one if needed) for the following:

Consecutive days worked in a range selected by a slicer with the primary ranges of consecutive days in a 7 or 14 day period.
- Days to be identified in the table (first and last) and broken out by weeks - M - S)
- Slicer to be used to identify the date range.
Cannot use standard WORKDAYS functions as the group in question works non-standard shifts in a manufacturing environment that works through weekends and holidays.
Data provides the date worked with hours worked and overtime but dates not worked are skipped.
- These missing dates must be able to be counted to identify consecutive days

Sample data is below. I have a calendar created within the Power BI file that is based on the min/max dates worked. The basic joins for this issue are in an image below the sample data.

Any assistance is greatly appreciated.

Empl ID	DateWorked	Sum of HoursWkd	Sum of Hours_OT
A123456	1/2/2023	8	0
A123456	1/4/2023	8	0
A123456	1/5/2023	8.2	0
A123456	1/6/2023	8.3	0
A123456	1/9/2023	8	1.2
A123456	1/10/2023	8.3	0
A123456	1/11/2023	8	0.5
A123456	1/12/2023	8.2	0
A123456	1/13/2023	8	1.1
A234567	1/1/2023	20.2	0
A234567	1/3/2023	8	4.1
A234567	1/4/2023	8	4.1
A234567	1/5/2023	8	4.3
A234567	1/9/2023	8	4.4
A234567	1/10/2023	8	4.2
A234567	1/11/2023	8	4.3
A234567	1/12/2023	8	4.3

topic Consecutive Days worked in 7 and 14 days based on a date slicer with days worked gaps in DAX Commands and Tips

Consecutive Days worked in 7 and 14 days based on a date slicer with days worked gaps