Get Max Date from Slicer and Next Three Dates

[GMajor67]

Hello all,

Because I cannot create a dynamic table based on a current slicer, I am instead using a measure to set create a "filter" with a column of 1s and 0s to replicate the outcome I am looking for. Essentially I want to have the maximum date selected from the current date slicer (which are all consecutive months) and then the following 3 consecutive months. So if I have say Feb-24, Mar-24 and Apr-24 selected in the slicer, I would want my "filter" measure to return 0's for all dates except Apr-24, May-24, Jun-24 and Jul-24.

This is the measure that I have tried, but it is just returning 0s for all dates.

I would appreciate any help you could give on this.

[v-tianyich-msft · Accepted solution]

Hi @GMajor67 ,

I made simple samples and you can check the results below:

Measure = VAR _select = SELECTEDVALUE('Table 2'[Date])
RETURN IF(MAX('Table'[Date])>=_select&&MAX('Table'[Date])<=EDATE(_select,3),1,0)

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[GMajor67]

Thanks @v-tianyich-msft - the key here too was to create a second date table to ensure all dates would appear in the visual but still leave filters active.

[SachinNandanwar]

What should happen if the user selection isnt continous ? For example use selects Mar24,June24 and Nov24.

[GMajor67]

Hi @SachinNandanwar , in that case the measure would return 1 for Nov-24, Dec-24, Jan-25 and Feb-25 and 0 elsewhere.

[bhanu_gautam]

@GMajor67 , Try using below measure

FourDatesFilter =

VAR PeriodStart = CALCULATE(MAX('Balance Sheet'[Date]), ALLSELECTED('Balance Sheet'))

VAR PeriodEnd = EDATE(PeriodStart, 3)

RETURN

IF(

    SELECTEDVALUE('Balance Sheet'[Date]) >= PeriodStart &&

    SELECTEDVALUE('Balance Sheet'[Date]) <= PeriodEnd,

    1,

    0

)

[GMajor67]

Thanks for this @bhanu_gautam ...this looks similar to variations I have tried before. With your version, I get 0 as outputs in the measure except for the very last date in Balance Sheet[Date] (which shows 1 as the output for FourDatesFilter), even if not selected in the slicer. Any more thoughts?