How to parametrize 'is in the previous' and 'is in the next' filters.

[AzureGuy]

Hi all,

I am trying to filter data I am getting from SQL DW. I have some parameters and I can't apply them to "is in the previous" and "is in the next" filters. I can apply parameters to other filters with no problem (is before, is after, equals,...).

Did you know how to do that ? Please look at this image for more info.

Thanks,

Azure Guy

[AllisonKennedy · Accepted solution]

You could try editing the M code manually and see if it accepts your parameter as a value, not sure if it will for this or not - but click on the View tab in Power Query Editor to turn on Formula Bar. Then apply the filter as per your screenshot with 'is in the previous' 3 'years' , just put any number as the placeholder. Then update the formula in the formula bar to replace the '3' with the name of your parameter. Make sure your data type for the parameter is number.

[AzureGuy]

Thanks Allison. This trick actually worked. It's weird we can't do that we the graphic tool but we can do that wit this workaround.

Anyway, thanks.

[AllisonKennedy]

@AzureGuy , glad it worked! There are lots of scenarios like this where it can be done by editing the M code manually, but not using the User Interface, so keep this trick up your sleeve. 🙂

[amitchandak]

@AzureGuy , you can use the relative date filter

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

Rolling formula with date table

Rolling 3 day = CALCULATE(sum(Table[Number]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),-3,Day))

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-3,Day))

or refer

https://www.youtube.com/watch?v=duMSovyosXE