Selected-Filtered value calculation

Hi rdehatheba55 ,

The issue you're experiencing is due to your current DAX measure summing up the entire "Days out of work" duration without considering whether those days fall within the selected date range from the calendar slicer. When filtering by January, for example, your measure still shows 105 days instead of calculating just the days in January that overlap with the injury period.

To address this, you can create a new DAX measure that dynamically calculates the number of overlapping days between the “Date removed from duty” and “Date returned to duty” for each record, but only within the selected range in the Calendar table. Here’s the measure:

Days out of work (filtered) =
VAR MinDate = MIN('Calendar'[Date])
VAR MaxDate = MAX('Calendar'[Date])
RETURN
SUMX(
    FILTER(
        IndAccidents,
        IndAccidents[Removed from duty] = "Yes"
            && NOT(ISBLANK(IndAccidents[Date removed from duty]))
    ),
    VAR StartDate = IndAccidents[Date removed from duty]
    VAR EndDate =
        IF(
            ISBLANK(IndAccidents[Date returned to duty]),
            TODAY(),
            IndAccidents[Date returned to duty]
        )
    VAR OverlapStart = MAX(StartDate, MinDate)
    VAR OverlapEnd = MIN(EndDate, MaxDate)
    RETURN
        MAX(0, DATEDIFF(OverlapStart, OverlapEnd, DAY) + 1)
)

This measure works by first retrieving the minimum and maximum dates from the calendar selection. Then, for each injury that resulted in a removal from duty, it checks for overlapping days between the removal period and the selected calendar range. The result is the total number of days actually falling within the filtered period, fixing the discrepancy you're seeing when changing the month filter.

Best regards,