Date Selector interaction with Date Slicer

[Anonymous]

Hi folks,

I'm struggling to get a "Between" Date Slicer to adjust its range to the start date I'm setting using a Date selector. Here are the details:

I have a date selector which is using a switch and a measure to filter out dates before the selected start date on all of my visuals:

           

Date Range = IF([Start Date]>=MAX('Calendar'[Calendar Date]),1,0)

This is working as intended but my users are requesting a timeline date slicer in addition to the Start date selector:

This is also working except for the fact that the lower bounds isn't being set by the Start date selected in the Date Range Selector.

Any Ideas on how to get this to work? Also I'm curious if there would there be a better way to filter the date range of my visuals using a dynamic start date selector. Having to use a measure as a flag in the visual filter of each idividual visual seems shody. Any help is incredibly appreciated, you're all amazing people 🙂

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Create a measure as below and make the slicer filtered by it. After that we should edit the interaction of the two slicers to make it work.

Measure = IF( MAX('Calendar'[Calendar Date])>= [Start Date],1,0)

For more details, please check the pbix as attached.

[Anonymous]

Thanks @v-frfei-msft!

That solution is perfect but it's not available on my version of Desktop 😞

I'll just wait until I get the update. Thanks for everyone's input.

[Anonymous]

Hi @Anonymous 

If I resolve your problem Mark it as a solution so that it will help others.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

[Anonymous]

Hi @Greg_Deckler  & @amitchandak,

Thanks for your replies! Unfortunately I can't share the original .pbix but I've created a sample file.

The main issue is that my Start date filter doesn't limit the Calendar Date timelime filter:

I'm open to different ways of setting up the date filtering if you have any suggestions!

Cheers,

[amitchandak]

@Anonymous 

I created a formula like this and added it to the table.

Measure = CALCULATE(COUNT('Contract Header'[Contract #]),USERELATIONSHIP('Calendar'[Calendar Date],'Contract Header'[Start Date]),CROSSFILTER('Calendar'[Calendar Date],Sales[Date],None))

File attached at after signature

[Anonymous]

So you want last 6 or 12 months of data from selected end date.

Create one date dimension which is not connected with your main table.

Create one slicer table for 6 months 12 months values in it.

Then create measure using switch statement and add condition for actual date column.

Switch(true(),selectevalue(slicer[col])="last 6 month" && (max(table[date]) <= today() && max(table[date])>= dateadd(max(date[date]),-6,month),1,0)

May be syntax is not correct but i have given you fair idea about solution.

Thanks
Pravin

[Greg_Deckler]

Hmm, this looks like an interesting issue although off of the top of my head I an not sure there is a solution. Can you post your PBIX or sample/example data. I think it would be interesting to look at.

[amitchandak]

When you select 12 months, it selects the correct end date, not the start date. Can you explain with example