Forum Discussion
Solved
Slicing Between Multiple Dates
I have a dataset with ServiceStart and a ServiceEnd date columns. I need a date slicer that will include records that would be considered 'active' based on the date selection. In this exam...
Hi JVal76 ,
You can follow the steps below to get it, please find the details in the attachment.
1. Create a measure as below
Flag = VAR _clientid = SELECTEDVALUE ( 'IntersectData'[ClientID] ) VAR _year = SELECTEDVALUE ( 'Dates'[Date].[Year] ) VAR _month = SELECTEDVALUE ( 'Dates'[Date].[MonthNo] ) VAR _yearmonth = VALUE ( _year & IF ( _month < 10, "0" & _month, _month ) ) VAR _client = CALCULATE ( MAX ( 'IntersectData'[ClientID] ), FILTER ( 'IntersectData', 'IntersectData'[ClientID] = _clientid && VALUE ( FORMAT ( 'IntersectData'[ServiceStart], "YYYYMM" ) ) <= _yearmonth && VALUE ( FORMAT ( 'IntersectData'[ServiceEnd], "YYYYMM" ) ) >= _month ) ) RETURN IF ( NOT ( ISBLANK ( _client ) ), 1 )2. Apply a visual-level filter on the table visual
Best Regards
Link has been updated to allow anyone with link.
https://drive.google.com/file/d/1dJ1PXwh4QI6v73-704JaA-MuouzxmrLw/view?usp=drive_link
Hi JVal76 ,
You can follow the steps below to get it, please find the details in the attachment.
1. Create a measure as below
Flag = VAR _clientid = SELECTEDVALUE ( 'IntersectData'[ClientID] ) VAR _year = SELECTEDVALUE ( 'Dates'[Date].[Year] ) VAR _month = SELECTEDVALUE ( 'Dates'[Date].[MonthNo] ) VAR _yearmonth = VALUE ( _year & IF ( _month < 10, "0" & _month, _month ) ) VAR _client = CALCULATE ( MAX ( 'IntersectData'[ClientID] ), FILTER ( 'IntersectData', 'IntersectData'[ClientID] = _clientid && VALUE ( FORMAT ( 'IntersectData'[ServiceStart], "YYYYMM" ) ) <= _yearmonth && VALUE ( FORMAT ( 'IntersectData'[ServiceEnd], "YYYYMM" ) ) >= _month ) ) RETURN IF ( NOT ( ISBLANK ( _client ) ), 1 )2. Apply a visual-level filter on the table visual
Best Regards