Forum Discussion
Show items between start and end dates
Hello!
I have run into a recurring situation where individuals are asking to be able to use a filter to see all items in a table that "exist" between a start and end date, but the date they select is neither a start or end date.
For example, in this chart, we see items 1 and 4 are both ongoing on Jan 23. So, an end user wants to be able to select Jan 23 and see these two items be returned in the chart. As we know, if the filter is using the start date as the field and we select, Jan 23, nothing would be returned.
I'd like to learn how to properly model this so that I can apply the lessons learned to multiple dashboards since this request continues to pop up more and more. Do I need a date table? If so, do I build a relationship between that table and my task table? I'm just not sure so that's why I'm reaching out.
Or, if anyone has any good resources about this topic, I'll take that as well!
Thanks in advance!
You could swap the MIN('Date'[Date]) for MAX('Date'[Date]), that should do it
9 Replies
You should definitely build a proper date table, that helps with all sorts of things such as time intelligence measures and making sure that all possible dates are available for a user to select from a slicer, not just the dates which appear in your data.
In this case you wouldn't want the date table to be related to your data table. As you said, that would filter just those tasks which started on a particular date.
You can create a measure and use that as a filter on the visual where you want to show tasks.
Is Task Visible = var startDate = SELECTEDVALUE('Table'[start date]) var endDate = SELECTEDVALUE('Table'[end date]) return IF( startDate <= MIN('Date'[Date]) && ( ISBLANK( endDate) || endDate >= MAX('Date'[Date]) ), 1, 0 )This will allow users to select either a specific date or a range of dates. Add it as a filter to only show rows when the value is 1
Thank you for your response! I have gone ahead and create the measure, but now I'm just a bit confused on where I'm applying it and what field I'm using as my date filter. So, you said to place the measure on the visual which I'm assuming you mean in the filter pane? If that's correct, do I need to set it to equal 1 in the filter pane? As for the filter drop down, which field am I using? A date field from the date table? If you had screenshots of how you'd apply this, that would be great. You don't have to use it on a Gantt chart obviously but seeing it would be a bit easier to see where you're coming from. I appreciate your response though!
Yes, place the measure on the filter pane like
You would use the Date field from your Date table on the slicer.
