powerbidaxdata-analysispowerbi-desktopmeasure

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

I'm new to PBI and DAX

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

enter image description here

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...

enter image description here

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)
Solution

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

    Table:

    enter image description here

    Date

    Date = CALENDARAUTO()

    enter image description here

    No relationships.

    enter image description here

    Measure:

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

    Table visual

    enter image description here

    Filter pane:

    enter image description here

    Working:

    enter image description here

    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