cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)