Single date slicer across multiple queries

[tempranello]

Hi there

I just want to make sure that I've not missed a trick here:

I'd like to present a page of visualisations and have a single time slicer filter all of the charts.  The trouble is that the queries upon which the charts are based are seperate (two related queries and one reference).  Each query has it's own 'Month ending' field that is a custom column of Date.EndofMonth([Created date]), but I cannot simply plop in a single time slicer that points to one of the 'Month ending' fields of one of the queries and have it affect the other charts.

Background:  The charts stem from different queries: All Tickets, and All Journals.  While each Ticket record is related (0:Many) to Journal records, none of the charts use this relationship as I'm plotting charts like ticket volumes over time, resolution by team, and total journal effort over time.  I also have a chart, Ticket Resolution by Team, that stems from a query that is a reference (rather than a duplicate) to the All Tickets. If I drop in a slicer that uses the All Tickets.Month ending, then it affects only the charts based on that query.

Am I missing a trick here?  Do I really need a slicer per query?, or is there a way that I relate all of the queries to a single 'Month ending' field to make all charts filterable from a single slicer?

Thanks for your help.

[mike_honey · Accepted solution]

I would add a New Table and use this formula to generate a calendar of all required dates (with the year ending in December):

Dates = CALENDARAUTO(12)

Then I would use Manage Relationships to add relationships between that new Dates table and your existing tables. 

Finally I would add a slicer, using the Dates.Date column.

[tempranello]

Thank you, Mike!

Taking your advice I created a calendar table.  Instead of creating a DAX-based calendar, I chose to create one in Power Query to give me more control.  I used the Power Query code here.

I then cleaned up my table relationships.  I found that slicing the date in the Calendar table wouldn't work as predicted if I had spurious inactive relationships floating about.  I cleaned up my table relationships to be as simple as possible and voila!  I really snazzy single slicer now drives the entire report!

Woohoo!

[mike_honey]

Sounds good - thanks for the whoohoo ...

More effort in the query and modeling end usually pays off down the track.

I'm a huge PQ fan too - my example was just the quickest way to get you going.  That script is a great starting point.