Challenging SLICER question. Need to use same slicer on many calendar tables.

[shophoney]

Hi I have a difficult question. I have 3 calendar tables.

dCalendar (this years dates)
dCalendar1YrAgo (last years date)
dCalendar2YrAgo (2 yrs ago date)

What I have in each table is this years date and then a formula to -364 days so that Sept 1/17 = Sep 2/16 (monday = Monday)

But I want to have a slicer control all the calendar tables at once. So when I want to slice August dates it will control all 3 tables and show august for each year,

I hope i made this clear.

Thanks

 

[gazpage]

Why would you want three calendar tables?

Good practice is usually to have one calendar table that has a unique ordered column at your most granular level, for example the dd/mm/yy, and then various other columns taken from that (e.g. Financial Year, Financial Quarter, Calendar Year, Calendar Quarter, Month, Weekday etc. Then if you want to look at 2 years ago, you just have a slicer on the Year and choose two years ago. Selecting August on a slicer for the Month column then returns just the August data.

I think you are making extra work for yourself otherwise.

 

[shophoney]

Hi thanks for the reply. What I’m trying to do is compare September 1 to September 30 this year to the same week days last year that may overlap into the end of August and 29 September 2016. I’m trying to do yearly comparisons of sales but with the same days of the week.

So to be clear I have created three calendar tables one with this year’s dates. And one with a formula of this year’s dates -364 and the same in the table for two years ago that way I can pull in the slicer with this year’s date and it will show the last two years of sales same days of the week

 

[gazpage]

You should still have one calendar table and then create a column with the week identifier, ie number from 1 to 52. You can easily do such comparisons once that is done.

 

[akice]

You should still have one calendar table and then create a column with the week identifier, ie number from 1 to 52. You can easily do such comparisons once that is done.

+1. Do what you want leveraging Dax, not model design. Only need (and want) 1 calendar table.