Selected-Filtered value calculation

[rdehatheba55]

I have the following table (IndAccidents) below from which I need to derive a power bi report.

Id	Incident Date	Incident Time	Date reported	Removed from duty	Date removed from duty	Date returned to duty	Days out of work
1	1/1/2025		1/1/2025	Yes	1/1/2025		105
2	1/1/2025		1/1/2025	No			0
3	2/1/2025		2/3/2025	Yes	2/10/2025	2/20/2025	10
4	1/15/2025		1/15/2025	Yes	3/31/2025		16
5	2/17/2025		2/17/2025	No			0
6	3/5/2025		3/5/2025	Yes	3/5/2025		42

I created a calculated column “Days out of work” as following while I was in the power query editor:

Days out of work = if [Date returned to duty] =null and [Date removed from duty] =null then 0

else

if [Date returned to duty] =null and [Date removed from duty] <>null then

Duration.Days(Date.From(DateTime.LocalNow())-Date.From([Date removed from duty]))

else

Duration.Days([Date returned to duty]- [Date removed from duty])

Based on the calculated column above, I created the following measures:

Total Injuries = COUNTROWS(IndAccidents)

Days out of work = sum (IndAccidents [Days out of work])

I also have a calendar table called calendar built based on the range of dates in the “Incident Date” column as following:

Calendar = CALENDAR (

                               DATE(YEAR(MIN(IndAccidents[Incident Date])),1,1),

                               DATE(YEAR(MAX(IndAccidents[Incident Date])),12,31)

                                )

A one-to-many relationship has been defined between the Calendar and the IndAccidents tables on calendar [Date] and IndAccidents [Incident Date].

My report looks like the one below as of 4/23/2025:

The Problem:

When I filter the table for the month of January, my expectation is to get the number of “days out of duty” within the filtered range, i.e. “Days out of duty” from January 1st to January 31st, (“Days out of duty totaling 31 days). Instead, I am still getting 105 which is the “Days out of duty” -to-date.

Similarly, when March is selected, I expect to see 26 days out of work. Instead, the result yielded is still 42, i.e. the value to date

Any help would be greatly appreciated.

rdehatheba55

[rdehatheba55]

The metric I am looking to capture is the total days of duty lost by all employees within a specific timeframe, usually monthly but at times quarterly/ annually. Regardless of when the incident occurred or the date the employee was removed from duty, I need to capture all the days lost within that specific timeframe. For instance, If there is an injury on 10/1/24 and the employee is removed from duty 1/10/2025 through 3/30/2025, I would want to see 28 days populate if I am filtering by the month of February.

[rdehatheba55]

DataNinja777, Thank you for your response. Your formula works.

Question however. How would I then derive the Days out of work to-date from your recommended dax formula?

[lbendlin]

I created a calculated column “Days out of work” 

That would require your data source to be in import mode, and you would need to have a daily refresh schedule.

Calendar = CALENDAR (

                               DATE(YEAR(MIN(IndAccidents[Incident Date])),1,1),

                               DATE(YEAR(MAX(IndAccidents[Incident Date])),12,31)

                                )

You could have used CALENDARAUTO().

Instead, I am still getting 105 which is the “Days out of duty” -to-date.

You created a calculated column. Your filter choices will not modify that number.

A one-to-many relationship has been defined between the Calendar and the IndAccidents tables on calendar [Date] and IndAccidents [Incident Date].

You will have to make that relationship inactive, and create a measure that calculates the number for each day in the Dates table across all IDs.

You can add a calendar hierarchy if you need to report this by month

Feel free to adjust the formula to decide if you want to include the first day or not.

[DataNinja777 · Accepted solution]

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,