is in Date range

[JaviGolden]

I have a matrix that I'm trying to use values from a table named 'Programs'.   

Id	Name	CurrencyCode	StartDate	EndDate
318	Program Name 11	USD	7/2/2023	10/15/2024
397	Program Name 66	GBP	10/10/2024	11/1/2024
376	Program Name 29	GBP	7/25/2024	11/7/2024
103	Program Name 97	GBP	1/24/2024	9/12/2024
489	Program Name 51	USD	8/28/2024	9/25/2024

I want the Name column to be the first column in the matrix.   I have a slicer that select a data range.  ie 1/1/2024 - 6/3/2024.  I want any program that is active at any point within the date range to be included in the matrix.   Based on the dates 1/1/2024-6/30/2024, I should have Program Name 11 and Program Name 97 in the matrix.   

I created this measure to verify if the program was within the selected date range:

IsWithinDateRange =

VAR Date_Selected_Min = MIN(Dates[Date]) -- Minimum date from slicer

VAR Date_Selected_Max = MAX(Dates[Date]) -- Maximum date from slicer

VAR ProgramStartDate = SELECTEDVALUE(Programs[StartDate]) 

VAR ProgramEndDate = SELECTEDVALUE(Programs[EndDate])     

RETURN

    SWITCH (

        TRUE(),

        ProgramStartDate >= Date_Selected_Min && ProgramStartDate <= Date_Selected_Max, TRUE,

        ProgramEndDate >= Date_Selected_Min && ProgramEndDate <= Date_Selected_Max, TRUE,

        ProgramStartDate <= Date_Selected_Max && ProgramEndDate >= Date_Selected_Min, TRUE, -- Overlap case

        FALSE

    )

The measure returns TRUE or FALSE correctly, however, it doesn't work as a filter on the visual.   

How do I accomplish this?   Thanks!  

[Kedar_Pande]

@JaviGolden 

You can try:

IsWithinDateRange =
VAR Date_Selected_Min = MIN(Dates[Date])
VAR Date_Selected_Max = MAX(Dates[Date])
VAR ProgramStartDate = SELECTEDVALUE(Programs[StartDate]) 
VAR ProgramEndDate = SELECTEDVALUE(Programs[EndDate]) 
RETURN
IF (
ProgramStartDate >= Date_Selected_Min && ProgramStartDate <= Date_Selected_Max
|| ProgramEndDate >= Date_Selected_Min && ProgramEndDate <= Date_Selected_Max
|| ProgramStartDate <= Date_Selected_Max && ProgramEndDate >= Date_Selected_Min, 
1, 
0
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Idrissshatila]

Hello @JaviGolden 

instead of true false, make it return 0,1 then use it to filter .

[Bibiano_Geraldo · Accepted solution]

Hi @JaviGolden ,
I just made a little changes in your measure, instead of TRUE or FALSE, i'm using 1 to represent True, and 0 to represent False. here's the updated measure:

IsWithinDateRange = 
VAR Date_Selected_Min = MIN(Dates[Date]) -- Minimum date from slicer
VAR Date_Selected_Max = MAX(Dates[Date]) -- Maximum date from slicer
VAR ProgramStartDate = SELECTEDVALUE(Programs[StartDate]) 
VAR ProgramEndDate = SELECTEDVALUE(Programs[EndDate])     
RETURN
    SWITCH (
        TRUE(),
        ProgramStartDate >= Date_Selected_Min && ProgramStartDate <= Date_Selected_Max, 1,
        ProgramEndDate >= Date_Selected_Min && ProgramEndDate <= Date_Selected_Max, 1,
        ProgramStartDate <= Date_Selected_Max && ProgramEndDate >= Date_Selected_Min, 1, -- Overlap case
        0
    )

Add the Measure to the Filters Pane:

• Select your matrix visual.
• Open the Filters pane.
• Drag the IsWithinDateRange measure to the Filters pane for the visual.
• Set the filter condition to IsWithinDateRange = TRUE.

Now your Matrix or Table visual should look like this when your select the date range (1/1/2024 - 6/3/2024.)

 

[danextian]

Hi @JaviGolden 

Check if this works for you. The details are in the attached pbix.

[lbendlin]

[value]  in CALENDAR([startdate],[enddate])

or simpler

[value] in Dates[Date]

or a bit more fancy

countrows(intersect(calendar([startdate],[enddate]),Dates[Date]))>0