Calculated field that uses dynamic filter or any way of doing th

Hi Grazi,

Thank you or your reply.

What you need to do is to load a 'Date table' from Excel as well as the data containing the case data. This date table can just have 1 column for all the dates in 2023 to be displayed in your slicer ie. all dates from 01/01/23 to 31/12/23. Call this field 'date range' in a sheet called 'Date table'.

1. Make sure there is no relationship between both tables in Power BI desktop by checking the data model.

2. Then create a table with columns for Case ID, Case Date and Date closed

3. Create a slicer based on the dates from the date table

4. Write the following DAX measure to show 1 if any case is within the date range or is still open (ie. where the date closed is blank) or 0 if it's not in the date range:

Within Date Range =

var rangeStart = FIRSTDATE('date table'[Date range])
var rangeEnd = LASTDATE('date table'[Date range])
return
IF(
SELECTEDVALUE(Sheet1[CaseDate])>=rangeStart
&&
SELECTEDVALUE(Sheet1[dateClosed])<=rangeEnd
||
SELECTEDVALUE(Sheet1[dateClosed])=BLANK(),
1,0)

This DAX formula is based on the formula you used in the 'Case within range' field in the top table of the Excel sheet you sent me

5. Finally drag the measure to the table to display a list of 0's and 1's

6. Now change the date range in the slicer to update the list

I hope this is a solution to your problem. Please let me know how you get on

Kind regards
Martin