Forum Discussion
JaviGolden
1 year agoFrequent Visitor
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...
- 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.)
Bibiano_Geraldo
1 year agoSuper User
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.)