Forum Discussion

sakamotothais's avatar
sakamotothais
Helper III
9 years ago

Dynamic filter for a measurement

Hi

 

I have a measure with today's date D 0 and a measure with yesterday's date D -1.

 

How can I get dynamic when I filter a date on a slicer?

For example, when selecting on filter day 26 bring in D 0 result of day 26 and in D -1 result of the previous day 25

If I select on filter day 25 bring in D 0 result of day 25 and in D -1 result of the previous day 24 And so on. Can someone help me?

 

thanks

Need Help
Show and Tell

6 Replies

    • sakamotothais's avatar
      sakamotothais
      Helper III
      9 years ago

      Thanks,

       

      But I'm really not able to put the formula all filter, or filter allselect in the formula with SUMX

       

      Please can anyone help me, structure the code for d-1. What to get yesterday's filter date?

       

       

      • MattAllington's avatar
        MattAllington
        Community Champion
        9 years ago
        sakamotothais wrote:
         


        But I'm really not able to put the formula all filter, or filter allselect in the formula with SUMX

         

         

          You can extract yesterday's filter date generally by using the piece of code MAX(Calendar[Date])-1 where Calendar[Date] is used to filter your visual.  It is normally used inside a calculate

         

         

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    9 years ago

    sakamotothais,

     

    Please refer to the steps below to achieve your requirement.

    1. Create a date table, and do not create relationship between this date table and your original table.
      Table = CALENDAR(DATE(2017,4,1),DATE(2017,6,30))
    2. Create a measure in your original table.
      Measure =
      var selectitemed = MAX('Table'[Date])
      return IF((1*(MAX(Table1[Date])-selectitemed)=0||1*(MAX(Table1[Date])-selectitemed)=-1),1,0)
    3. Add this measure to your visual filter like below.

    Result.

     

    Regards,

    Charlie Liao

    • sakamotothais's avatar
      sakamotothais
      Helper III
      9 years ago

       

       

      Thanks for the example, it worked well.


      But it still is not what I need.


      I need to separate. A measure that contains today's date, and a measure that contains yesterday's date according to the date filter.
      Then check the variance in the graphs between the two results.

       

      Thanks