Forum Discussion

JaviGolden's avatar
JaviGolden
Frequent Visitor
1 year ago
Solved

is in Date range

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

IdNameCurrencyCodeStartDateEndDate
318Program Name 11USD7/2/202310/15/2024
397Program Name 66GBP10/10/202411/1/2024
376Program Name 29GBP7/25/202411/7/2024
103Program Name 97GBP1/24/20249/12/2024
489Program Name 51USD8/28/20249/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!  
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

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

     

     

     

     

5 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

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

     

     

     

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

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

     

    or simpler

     

    [value] in Dates[Date]

     

    or a bit more fancy

     

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