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
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, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

1 REPLY 1
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, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,730)