Forum Discussion
Date Filter in between dates
Because you have a relationship with the StartDate, it is filtering the data only based on that column. To get the functionality you are looking for you need to take that relationship off the table by one of a few ways -
1. Deleting that relationship (probably not recommended if you need to do other analyses on StartDate)
2. Add a new Date table with DAX to be used only in your slicer with something like SlicerDates = VALUES('Date'[Date]) //or whatever you Date column is
3. Use CROSSFILTER() in a calculate to turn off that relationship just for one measure
#2 if probably the simplest. If you do that, you can then use a measure like this in your table visual (or as a Filter on your table visual). Replace "Table" with your actual table name.
Show In Table = VAR __minslicer = MIN ( SlicerDates[Date] ) VAR __maxslicer = MAX ( SlicerDates[Date] ) RETURN IF ( ISBLANK ( COUNTROWS ( FILTER ( Table, Table[Start Date] <= __maxslicer && Table[End Date] >= __minslicer ) ) ), 1 )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi amitchandak ,
In the images below, because the Start Date of 19/05/2019 and End Date of 31/12/2020 is within the date filter of 01/10/2019 and 31/03/2020, this should display in the table.
That's what I'm trying to do. Currently it works only if 19/05/2016 (Start Date) is within 01/10/2019 and 31/03/2020 which therefore wouldnt display.
Microsoft EmployeeBecause you have a relationship with the StartDate, it is filtering the data only based on that column. To get the functionality you are looking for you need to take that relationship off the table by one of a few ways -
1. Deleting that relationship (probably not recommended if you need to do other analyses on StartDate)
2. Add a new Date table with DAX to be used only in your slicer with something like SlicerDates = VALUES('Date'[Date]) //or whatever you Date column is
3. Use CROSSFILTER() in a calculate to turn off that relationship just for one measure
#2 if probably the simplest. If you do that, you can then use a measure like this in your table visual (or as a Filter on your table visual). Replace "Table" with your actual table name.
Show In Table =
VAR __minslicer =
MIN ( SlicerDates[Date] )
VAR __maxslicer =
MAX ( SlicerDates[Date] )
RETURN
IF (
ISBLANK (
COUNTROWS (
FILTER (
Table,
Table[Start Date] <= __maxslicer
&& Table[End Date] >= __minslicer
)
)
),
1
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi mahoneypat ,
Many thanks for your reply.
I think thats what I need, except, I may have described it badly but where in the formula it says > Start Date and < End Date.
I would need it so if any part of the date crosses then it needs to be included in my matrix. I hope that makes sense?
As below, it starts in 2016 and ends greater than 31/03/2020 (31/12/2028), but because it includes it somewhere in its timeline it would need to be there.
Thanks again
- mahoneypat
Please let me know if you tried the one I suggested. By doing Start < Max and End>Min should include anything that is "Active" over the slider date range. If you want only things that Started and Stopped within the date range, just change it to Start>=Min and End <=Max.
Regards,
Pat
Thankyou mahoneypat for your help