How to use DAX to filter rows based on a date slicer

Question

I'm new to PBI and DAX

I've inherited a PBI report that uses the MS timeline slicer custom visual.

The problem I'm trying to resolve is to only show data in a table where any period inside the date/date range selected falls within the StartDate and EndDate of a table of data.

Based on the following subset table of data...

• If February 2024 was selected in the Timeline Slicer than only rows with Id 1 would be shown.

• If July 2024 was selected in the Timeline Slicer than only rows with Id 3 would be shown.

• If September 2024 to January 2025 was selected in the Timeline Slicer than only rows with Id 4 would be shown.

• If January 2025 was selected in the Timeline Slicer than no rows would be shown.

Including a 1-2-many relationship between the Calendar and Data table isn't going to work here. I'm thinking the way to do would be to have two measures.

One that will hold the value of the MIN date selected, and another to hold the MAX date selected.

SelectedMinDate = DATEVALUE(MIN('Calendar'[Date]))

SelectedMaxDate = DATEVALUE(MAX('Calendar'[Date]))

And then use this in a computed column to filter out the data.

But that's as far as I got.

What do I need further?

This is the DDL for the sample data set

DROP TABLE IF EXISTS dbo.Test1 

CREATE TABLE dbo.Test1
(
    Id          INT IDENTITY(1,1) ,
    Activity    VARCHAR(255) ,
    StartDate   DATE ,
    EndDate     DATE 
);

INSERT INTO dbo.Test1 
    (Activity,StartDate,EndDate)
SELECT 
    T.Activity, 
    T.StartDate ,
    T.EndDate 
FROM (VALUES('Test1', '20240101', '20240430'),
            ('Test2', '20240301', '20240630'),
            ('Test3', '20240501', '20241031'),
            ('Test4', '20241101', '20241231')
      ) T(Activity,StartDate,EndDate)

Answer 1

I think your test data has some errors in so I will assume you mean what I think. Here is a simple solution.

Table:

Date

Date = CALENDARAUTO()

No relationships.

Measure:

Measure = 
VAR x = VALUES('Date'[Date]) 
VAR y = CALENDAR(MIN('Table'[StartDate]), MAX('Table'[EndDate]))
RETURN 
COUNTROWS(INTERSECT(x,y))

Table visual

Filter pane:

Working:

Be aware of arbitrary shaped sets with this type of date analysis (not needed in this example but possible elsewhere in your analysis).

https://www.youtube.com/watch?v=bGVLguWf4Ls&t=1s