Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date Selector interaction with Date Slicer

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 🙂

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

8 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

       

       

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago
        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