cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
steph_io Regular Visitor
Regular Visitor

How to use a numeric parameter to filter a date range

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: How to use a numeric parameter to filter a date range

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.

7.PNG

 

Result: previous 1 month to next 1 month

8.PNG

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

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

1 REPLY 1
Highlighted
Community Support Team
Community Support Team

Re: How to use a numeric parameter to filter a date range

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.

7.PNG

 

Result: previous 1 month to next 1 month

8.PNG

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

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)