Staff availability between 2 dates - modification to measure required

Hi deevo ,

To modify your current measure for summing the "Assignment (hrs)" within overlapping assignment periods, we need to ensure the DAX logic efficiently handles large datasets, like your 3-million-row dataset. The objective is to calculate the total assignment hours for any overlapping assignments between the "Assignment Start Date" and "Assignment Finish Date," ensuring that every relevant assignment within that date range is included in the sum.

Here's a refined DAX measure that calculates the Assignment Period Availability by subtracting the overlapping "Assignment (hrs)" from the "Capacity (hrs)". The measure uses variables to define the date range and applies a filtered context to sum all overlapping assignment hours within that range.

Assignment Period Availability between Assign Start and End dates (hrs) =
VAR StartDate = MIN('Data 2 - Staff Assignments By Day'[Assignment Start Date])
VAR EndDate = MAX('Data 2 - Staff Assignments By Day'[Assignment Finish Date])

RETURN
CALCULATE(
    [Capacity (hrs)] - 
    SUMX(
        FILTER(
            'Data 2 - Staff Assignments By Day',
            'Data 2 - Staff Assignments By Day'[Assignment Start Date] <= EndDate &&
            'Data 2 - Staff Assignments By Day'[Assignment Finish Date] >= StartDate
        ),
        'Data 2 - Staff Assignments By Day'[Assignment (hrs)]
    ),
    FILTER(
        'Dim Date FY24-25',
        'Dim Date FY24-25'[Date] >= StartDate &&
        'Dim Date FY24-25'[Date] <= EndDate
    )
)

In this measure, we start by defining two variables, StartDate and EndDate, to capture the minimum "Assignment Start Date" and maximum "Assignment Finish Date" from the current row context in the 'Data 2 - Staff Assignments By Day' table. These variables set the boundaries for the date range we want to evaluate. The CALCULATE function is then used to adjust the filter context by applying a SUMX over a filtered subset of the 'Data 2 - Staff Assignments By Day' table. The FILTER inside SUMX identifies rows where the assignment periods overlap with the current date range. Specifically, it checks that the "Assignment Start Date" is before or equal to the EndDate and that the "Assignment Finish Date" is after or equal to the StartDate.

Finally, the outer FILTER on the 'Dim Date FY24-25' table ensures that the measure evaluates only within the desired date range from the calendar table. This approach ensures that overlapping assignments are correctly summed, even when there are multiple assignments within the same time period. The use of variables and efficient filtering helps optimize performance for large datasets by reducing the number of rows evaluated during each calculation.

Best regards,