Using a single slider to filter two different date columns in the same table

[AxlCox]

Some of the data I work with is essentially a list of tickets with Open and Closed dates. 

The data I have, in simple terms, is quantites of tickets. I'm being asked to create a filter (slicer) where a single date range can be set and have all of the card widgets with these totals change accordingly (created ticket totals one one card,  closed ticket totals on another).

Prior, I've been using separate sliders - one working off of the created date and the other off of the closed date to control the respective cards. Surely there's a way to have one slider control both, but still have each vizualization read the correct data? 

It seems like I would need a date table and just use that for the slicer, creating a relationship to, say, the created date maybe? 

[v-yalanwu-msft · Accepted solution]

Hi, @AxlCox ;

Yes, you need to create a separate date table that is unrelated to the original table. Then create two measures respectively to calculate the number of open and closed. I created a simple example with the same principle.

count = CALCULATE(COUNTROWS('table'),FILTER('table',[open date]>=MIN('slicer'[Date])&&[close date]<=MAX('slicer'[Date])))

The final output is shown below:

If not ok ,can you share more scenario or screenshots about  more details?
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AxlCox]

OK - I didnt even think I had to bring measurements into it. I was thinking the date table would just act as the "master control" for the slicer....or can it? I was using filters to have the cards show totals based on the ticket state. If I'm doing that do I even need to use measurements?

I was just thinking the slicer would set the "global" date range and all of the other vizual filters would show what I need them to....

[jdbuchanan71]

@AxlCox 

You would need a date table and you would link it to your ticket table on both the created date and the closed date.

Only one of the relationships can be active so let's assume it is the one to the created date.

When writing the measure to count tickets based on the closed date you would activate the relationship there.  Something along these lines.

Tickets by closed date =
CALCULATE (
    COUNTROWS ( TicketsTable ),
    USERELATIONSHIP ( TicketsTable[Closed Date], Dates[Date] )
)

Your main ticket count would be off of Created Date so that would just be

Ticket Count = COUNTROWS ( TicketTable )

 Then the slicer from the date table would control both measures and show the correct counts.