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
Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Check if this blog can help : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Appreciate your Kudos.
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.
Anonymous , One of the solution was there in my HR blog where we have Start date end date join to the same date calendar.
This one try with a date table not joined or use cross filter
measure = var _max = maxx(allselected(Date),Date[Date]) var _min = minx(allselected(Date),Date[Date]) return calculate(sum(Table[Data]), filter(Table,(Table[Start Date]<=_max && Table[Start Date]>=_min )|| ( Table[end Date]<=_max && Table[end Date]>=_min))) ///////////////////Or measure = var _max = maxx(allselected(Date),Date[Date]) var _min = minx(allselected(Date),Date[Date]) return calculate(sum(Table[Data]), filter(Table,(Table[Start Date]<=_max && Table[Start Date]>=_min ) && ( Table[end Date]<=_max && Table[end Date]>=_min)))Hi amitchandak
Thankyou, thats a great help.
Regarding where it says greater than start date and less than end date, do you know how this could be so if any point of the start and end dates touch the dates in the filter then include?
Thanks
Liam
Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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 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
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