slicer filter with time interval

[Nimmi]

Hi Team,

I have to create a slicer filter with date column intervals, colunm name current_end_date in my table

if (interval([Current End Date] - today(), 'd') < 0, 'expired',
if (interval([Current End Date] - today(), 'd') > 0  and interval([Current End Date] - today(), 'd') < 30, 'less than 30 days',
if (interval([Current End Date] - today(), 'd') > 31 and interval([Current End Date] - today(), 'd') < 60 , 'less than 60 days',
if (interval([Current End Date] - today(), 'd') > 61 and interval([Current End Date] - today(), 'd') < 90 , 'less than 90 days',
'more than 90 days'))))

like this kind of calculations. So the Slicer should display 5 options like above image.

Please help me on this.

[v-jiascu-msft · Accepted solution]

Hi @Nimmi,

You can add a column and try this formula.

Column =
VAR interval =
    DATEDIFF ( [Current End Date], TODAY (), DAY )
RETURN
    SWITCH (
        TRUE (),
        interval < 0, "expired",
        interval > 0
            && interval < 30, "less than 30 days",
        interval > 31
            && interval < 60, "less than 60 days",
        interval > 61
            && interval < 90, "less than 90 days",
        "more than 90 days"
    )

Best Regards,

[shakeelsyed]

Create a new calculated column with "IF" statement and use the same formula you mentioned below.

Hope that resolve your query.

Regards,

Shakeel