Use a Slicer to show items older than the selected days

[omdavies]

Hi,

I have a table of items that have a 'last updated date' column

I would like to create a method to filter items on a report to those that are older than x number of days with x being a selection either in a list or a what if input.

Is there a way to achieve this ?

Thanks

[sturlaws]

Hi @omdavies,

you can achieve this by first creating a stand-alone table with days, which you use as values for the slicer. This table can be generated like this

Number of days = GENERATESERIES(0;45;1)

Then create a measure something like this:

FilterDays =
VAR _X =
    MAX ( 'Number of days'[Value] )
VAR _lastTimestamp =
    CALCULATE ( MAX ( 'Table'[TimeStamp] ); ALL ( 'Table'[TimeStamp] ) ) + 1
RETURN
    IF ( SELECTEDVALUE ( 'Table'[TimeStamp] ) < _lastTimestamp - _X; 1; 0 )

Add this measure to the filter pane of you visual and set to filter on >= 1

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.