Dynamic Filter that changes with date slicer

[rjg2g11]

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!

[v-yulgu-msft]

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]

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]

Hi @rjg2g11,

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

regards,
Yuliana Gu

[rjg2g11 · Accepted solution]

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 )