Forum Discussion
Selected-Filtered value calculation
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
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,
