Filter Data Between Project Dates

I’ve got a report for analysing our support ticket data, a condensed version of which is attached.

We’ve had a request for a drop down to select a project phase, which each have a start/end date, to view the data for tickets created in that time period. Different projects can cross over in dates, so it’s not a case of a single date belonging to a single project phase. I’m really struggling to get my head around what relationships/DAX I need to accomplish this.

The project dates are entered into the pbix below and an extract of the tickets. Any help/advice is appreciated.

Support Ticket Analysis eDNA.pbix (780.4 KB)
Tickets.xlsx (29.9 KB)

I’ve found a way to do this. Instead of having a start & end date on my projects, I create a row for each date between the 2 in power query using the below in a customer column:

{ Number.From([StartDate])… Number.From([EndDate]) }

Expand this list to new rows and change the type to date.

Then a create a relationship between this new date column and my dates table, the slicer then works on the report.

Support Ticket Analysis eDNA.pbix (782.9 KB)


Jamie, this is really cool. You make every IT Manager everywhere proud in how you marched through this effort, self-solved, and shared the results in the way you did. How did this solution scale once the production version was updated?