Forum Discussion
Date Filter in between dates
Hi,
I have a start date and an end date column in my bookings table. Currently the relationship runs to only the start date.
This is therefore currently only displaying start dates within the relative date filter that I'm displaying. What I want to do is when the two date filters are selected by the user, it displays any booking within the start date and end date column. Would somebody be able to please help?
My aim is to ensure the example below is catered for in the report.
Thanks in advance
Liam
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
9 Replies
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)))
