Forum Discussion

rjg2g11's avatar
rjg2g11
Advocate I
8 years ago
Solved

Dynamic Filter that changes with date slicer

Hello, 

 

I have a selection of measures that look at a share over the last four weeks. To achieve this, i have created a custom column filter; 

 

Latest Week = IF(EPOS[Week End Date] = MAX(EPOS[Week End Date]), 1, 0)

 

I apply this to only bring back the latest week and then apply '-7' etc ect to then bring back previous weeks. This is static.

 

What i want to do is apply a time slicer to the report, so when i change the max date of this, the measure for the latest week also changes, and then so does the other measures. 

 

For example, 

 

Slicer max date set at 08/10/2017 - which means that max date for the measure is 08/10/2017

Change slicer date to 01/09/2017 - max date now changes for the latest week filter, showing data for the 01/09/2017 in the measure

 

Cheers!

 

Need Help
  • rjg2g11's avatar
    rjg2g11
    8 years ago

    Thanks v-yulgu-msft for your help, i have managed to solve this one using 

     

    Share - Reportable Owner Max Week = 
CALCULATE (
    [Share - Reportable Owner],
    FILTER (
        ALL ( Dates[Week End Date] ),
        Dates[Week End Date] = MAX( Dates[Week End Date])
    )
)


--Where [Share - Reportable Owner] is; 

Share - Reportable Owner = 

VAR Volume =
    SUM(EPOS[Base Unit])
VAR AllVolume =
    CALCULATE ( SUM ( EPOS[Base Unit] ), ALL('EPOS'[Reportable Owner]) )

RETURN
    DIVIDE ( Volume, AllVolume )

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi rjg2g11,

     

    You may need a calendar table, add [Date] column from calendar into slicer. 

     

    Then, the max date selected should be: Max date=MAX(Calendar[Date])

     

    Your dynamic measure (not calculated column) for latest week should be: 

    Latest Week = IF(Max(EPOS[Week End Date]) = [Max date], 1, 0)

     

    Best regards,
    Yuliana Gu

    • rjg2g11's avatar
      rjg2g11
      Advocate I
      8 years ago

      Hello 

       

      Thanks for the reply v-yulgu-msft

       

      I have tried this and had limited success. It is bringing back all the rows as 1. At the moment, i think its calculating the share from between the MIN and MAX Date of the date slicer. I just want to look at that MAX date only.

       

      So, i will have a Measure that shows the Share % of the MAX date only on the slicer. 

       

      Thanks!

       

       

       

       

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        Hi rjg2g11,

         

        Could you please provide detailed sample data and if possible show us the desired output with an image?

         

        regards,
        Yuliana Gu