Use Case: Next 30 Days Technician Availability and Scheduling

A practical approach for preparing technician availability data and enabling rolling future-date filters such as Next 7, Next 15, and Next 30 Days.

Overview

Technician scheduling is inherently forward-looking. Scheduling coordinators need to know which technicians are available tomorrow, next week, or during the next 30 days so that new appointments can be assigned before available capacity is consumed.

Traditional relative-date filters are commonly designed around historical periods such as Yesterday, Last 7 Days, Last 30 Days, Month to Date, or Year to Date. In this use case, the requirement is different: users need a continuously moving future window such as Next 7 Days, Next 15 Days, or Next 30 Days.

The solution combines technician booking and break information with technician working-slot data and a generated Calendar Date Dimension. The calendar derives a numeric DayOffsetFromToday value for each date, allowing a future relative-date requirement to be handled using a standard numeric range filter.

Business Requirement

The objective is to prepare a technician-per-day availability dataset that shows scheduled shift hours, booked hours, break or non-working hours, and remaining available hours, while also identifying how far each date is from the current date.

The resulting analysis should allow scheduling teams to answer questions such as:

• · Which technicians still have available hours during the next 7 days?
• · Which technicians have open capacity during the next 30 days?
• · How much available technician capacity exists by branch?
• · Which technicians are close to being fully booked?
• · How much of each technician's scheduled shift is booked, blocked, or still open?

Solution Approach

The core design is to calculate the number of days between today and each reporting date during SSDP data preparation. Once the date is represented as a simple numeric offset, the dashboard can use ordinary numeric filtering instead of depending on a built-in forward relative-date preset.

 

 

Target Data Grain

 

Establishing a common reporting grain before joining the datasets is essential. Scheduling data may contain many booking and break rows for the same technician and date, while Working Slot data may contain one or more shift rows. Both sides should therefore be normalized to technician-day level before the final join.

If the Working Slot source already contains exactly one row per technician per day, no additional aggregation is required. If multiple shifts can occur on the same day, aggregate them before joining to avoid duplicating daily booking and break measures.

Source Data

1. Technician Scheduling

DT_TechnicianScheduling stores technician appointment bookings and non-working events such as lunch breaks, meetings, scheduling holds, or other blocked time. A technician can therefore have several rows on the same day.

 

2. Technician Working Slot

DT_Availability_Technician_WorkingSlot stores the rostered working window for each technician, including shift start and end time, branch, and total working hours.

 

How the Forward-Date Logic Works

DayOffsetFromToday represents the position of each date relative to the current date. Negative values are historical, zero represents today, and positive values represent future dates.

 

The dashboard can then use the same field for multiple forward-looking ranges:

 

Data Model

 

Implementation in Smarten SSDP

The implementation can be understood in five phases: prepare the source data, build the relative calendar, prepare technician-day measures, assemble the availability dataset, and publish the final output for dashboard filtering.

 

Step 1: Load and Standardize Source Fields

Load DT_TechnicianScheduling and DT_Availability_Technician_WorkingSlot into SSDP. Convert StartDate from text, where applicable, to a true Date data type using the source format dd-MMM-yyyy. All downstream date calculations and joins depend on a valid Date field.

Also ensure that OccupiedHours, NonWorkingHours, and TotalHoursWorking are numeric. Blank or space-filled placeholders should be handled during preparation so that later aggregations do not produce unexpected results.

 

Step 2: Generate the Calendar Date Dimension

Create a separate Calendar Date Dimension using a Custom Query or Spark SQL query. The calendar should cover the historical and future planning horizon required by the source data. It should not end at +30 days if records beyond the default Next 30 Days view must remain classified in the underlying dataset.

 

The 90-day future range above is only an example. The actual calendar horizon should be aligned with the period retained for scheduling and reporting.

Step 3: Derive DayOffsetFromToday

Add a custom column that calculates the number of days between the current date and CalendarDate.

 

Negative values represent past dates, 0 represents today, and positive values represent future dates.

Step 4: Add Date Classification Fields

Create a DateCategory label for display and, if useful, an IsNext30Days convenience flag. DayOffsetFromToday remains the primary field for flexible Next 7, Next 15, and Next 30 Days filtering.

 

 

Step 5: Classify Booked and Non-Working Time

DT_TechnicianScheduling identifies rows using TypeFlag. Working rows contribute OccupiedHours, while Non Working rows contribute NonWorkingHours for breaks or other blocked activities. Confirm that these measures are populated consistently before aggregation.

Step 6: Aggregate Scheduling to Technician-Day Level

Collapse the booking and break rows into one row per technician per date. A GROUP BY query should be used because a windowed Sum operation may repeat a group total across every original row without actually reducing the row count.

 

 

Step 7: Normalize Working Slot to Technician-Day Level

Confirm that Working Slot contains one row per ResourceId + StartDate. If multiple shifts can occur on the same date, aggregate those shifts before joining them to daily booking totals.

 

 

Step 8: Join Working Hours with Booked and Break Hours

Join technician Working Slot data with the daily Scheduling aggregate using ResourceId and StartDate. Use Working Slot as the left side of the join so that rostered technicians remain in the result even when no appointment or break has yet been recorded.

 

Step 9: Calculate Available Hours

Available capacity is calculated by subtracting booked and non-working time from scheduled shift hours.

 

For example, an 8.5-hour shift with 5.0 booked hours and 1.0 break hour results in 2.5 available hours.

Step 10: Join the Relative Calendar

Join DT_AvailabilityCalendar to the technician-day availability dataset using StartDate = CalendarDate. This stamps every technician-day row with its relative position from today.

 

 

Figure 1: Example SSDP output after preparing technician-day availability measures

 

Step 11: Publish the Final Dataset

Remove intermediate fields not needed for reporting, such as RawAvailableHours and duplicated join keys, and publish the dashboard-ready dataset as DT_TechnicianAvailability_Next30Days.

The published dataset should retain ResourceId, TechName, BranchOfficeName, StartDate, ShiftHours, BookedHours, BreakHours, AvailableHours, DayOffsetFromToday, DateCategory, and IsNext30Days.

Step 12: Configure the Forward-Date Filter

Bind the dashboard filter to DayOffsetFromToday as a numeric range. Set the default range to 0-30 to show today through the next 30 days. The same field can support additional quick selections such as 0-7 and 0-15.

 

IsNext30Days can optionally be exposed as a simple Yes/No toggle. Because StartDate remains available, users can still apply a conventional date filter for historical or custom-period analysis where required.

Expected Output

The final output contains one row per technician per date, combining technician capacity measures with the forward-date classification required by the dashboard.

 

With DayOffsetFromToday filtered between 0 and 30, the first two rows remain visible. The third row remains in the underlying dataset but is excluded from the default Next 30 Days view because it is 50 days from today.

Refresh and Operational Considerations

Keep the Relative Window Dynamic

DayOffsetFromToday is calculated when the dataset is refreshed. A record that has an offset of 10 today should have an offset of 9 tomorrow. The Calendar Dimension and dependent final dataset should therefore refresh regularly, typically once per day, so the rolling future window moves with the current date.

Data Considerations

• · Multiple shifts per day: aggregate Working Slot to technician-day level before joining daily booking totals.
• · Multiple branches: define the required branch reporting rule if a technician can work in more than one branch on the same day.
• · Missing scheduling records: the left join from Working Slot keeps rostered technicians visible even when no booking or break exists.
• · Bookings without a working slot: the current design treats Working Slot as the driving dataset, so scheduling rows without a corresponding rostered shift are excluded unless a separate reconciliation rule is added.
• · Over-booking: floor AvailableHours at zero so over-booked days do not display negative availability.
• · Calendar horizon: ensure the Calendar Date Dimension extends across every date that needs relative-date classification, including dates beyond the default Next 30 Days view.

Conclusion

This use case demonstrates how Smarten SSDP can support forward-looking relative-date analysis by deriving a numeric day offset from the current date. By combining rostered working hours, appointment bookings, non-working time, and a Calendar Date Dimension, the final dataset provides both technician capacity and the relative-date information required for future-period filtering.

The dashboard can then support flexible selections such as Next 7 Days, Next 15 Days, and Next 30 Days using a standard numeric range on DayOffsetFromToday, enabling scheduling teams to identify open technician capacity before future schedules fill up.

Tags: Date Filtering, scheduling, Forward dates filter, Future date filter,