Forum Discussion
Advanced Date Filtering for Active rows at any date during the period
Hi bridgwan ,
Sorry for my misunderstanding, you may try this measure:
Measure = VAR _sele = YEAR ( MAX ( 'Calendar'[Date] ) ) * 100 + MONTH ( MAX ( 'Calendar'[Date] ) ) RETURN IF ( _sele >= YEAR ( MAX ( 'Table'[Start Date] ) ) * 100 + MONTH ( MAX ( 'Table'[Start Date] ) ) || _sele <= YEAR ( MAX ( 'Table'[End Date] ) ) * 100 + MONTH ( MAX ( 'Table'[End Date] ) ), 1 )Then apply the measure to filter pane(set as "is 1"), the final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi bridgwan ,
According to my understanding, you want to dynamically set a flag or filter data by some conditions such as :
period 1 for : Row Start Date < Range Start Date and the Row End Date < Range End Date
period 2 for : Row Start Date > Range Start and < End Dates and the Row End Date > Range End Date
period 3 for : Row Start Date > Range Start Date and Row End Date < Range End Date
period 4 for : Row Start Date < Range Start Date and Row End Date > Range End Date
Based on my test, if you apply Advanced filtering to visuals, it could not be dynamically, everytime you need to change date range.
So I suggest you need to create a new calendar table for slicer which used for "dynamic":
Calendar = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date])) Then use the following formula to create a measure:
Flag =
VAR _rangeStart =
MIN ( 'Calendar'[Date] )
VAR _rangeEnd =
MAX ( 'Calendar'[Date] )
RETURN
SWITCH (
TRUE (),
MAX ( 'Table'[Start Date] ) < _rangeStart
&& MAX ( 'Table'[End Date] ) < _rangeEnd, "period 1",
MAX ( 'Table'[Start Date] ) > _rangeStart
&& MAX ( 'Table'[Start Date] ) < _rangeEnd
&& MAX ( 'Table'[End Date] ) > _rangeEnd, "period 2",
MAX ( 'Table'[Start Date] ) > _rangeStart
&& MAX ( 'Table'[End Date] ) < _rangeEnd, "period 3",
MAX ( 'Table'[Start Date] ) < _rangeStart
&& MAX ( 'Table'[End Date] ) > _rangeEnd, "period 4"
)
You could use Multi-row card visual to display different period like this:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply. I suspect that I did not word my query clearly enough.
I have a database table with two date columns - Start Date and End Date. I need create a splicer to filter the rows in a table visualisation by month where at least one of the dates in the range is within the selected month.
Month Selected in the Slicer: Oct 2021
The rows that should be available in the table will included all of the following examples
1. Start Date: 1st Sept 2021 - End Date: 17th Oct 2021
2. Start Date: 12th Oct 2021 - End Date: 10th December 2021
3. Start Date: 6th Oct 2021 - End Date: 23rd Oct 2021
4. Start Date: 15 Aug 2021 - End Date: 20 Nov 2021
Hi bridgwan ,
Sorry for my misunderstanding, you may try this measure:
Measure = VAR _sele = YEAR ( MAX ( 'Calendar'[Date] ) ) * 100 + MONTH ( MAX ( 'Calendar'[Date] ) ) RETURN IF ( _sele >= YEAR ( MAX ( 'Table'[Start Date] ) ) * 100 + MONTH ( MAX ( 'Table'[Start Date] ) ) || _sele <= YEAR ( MAX ( 'Table'[End Date] ) ) * 100 + MONTH ( MAX ( 'Table'[End Date] ) ), 1 )Then apply the measure to filter pane(set as "is 1"), the final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.