How to use a numeric parameter to filter a date range

[steph_io]

I have a CAGR calculation that uses parameters of Number of months to base the rate calculation, and Number of future months to plot the forecasted revenue.  How do I use these paraments to filter the page and/or visual to only show the X number of past months and X number of future months in the chart?

I do have a date table, but the parameter table does not link to it. The filter of the visual does not accept a measure (measure is used to capture the value entered in the parameter).  Any ideas?

[v-shex-msft · Accepted solution]

Hi @steph_io,

For your scenario, you can refer to below steps to filter based on slicer.

1. Create a parameter table to store month number.

2. Write a measure to check the current records based on filtered data range, then add tag to mark this record.

Tag =
VAR selected =
    SELECTEDVALUE ( 'Para Table'[Number] )
VAR current_date =
    MAX ( 'Table'[Date] )
RETURN
    IF (
        current_date
            >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - selected, DAY ( TODAY () ) )
            && TODAY ()
                <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + selected, DAY ( TODAY () ) ),
        1,
        0
    )

3. Drag this measure to visual level filter.

Result: previous 1 month to next 1 month

Notice: above measure will filter the date range previous X month to next X month.(X based on slicer)

Regards,

Xiaoxin Sheng