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'.    Id Name CurrencyCode StartDate EndDate 318 Program Name 11 USD 7/2/2023 10/15/2024 397 Program N...
  • Bibiano_Geraldo's avatar
    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.)