Dynamic filtering

[bijntjede2e]

Hi,

I was wondering if I can apply a dynamic filter in Desktop.

A table (directly to SQL Server OLTP DB) has column [Year] (= transactional year) and I added custom column [CurrYear] to that recordset in the Power BI Query Editor which holds the current year.

Another table in Power BI holds the time "dimension" (from year 1990 until year 2030).

I have a slicer that pulls the years from this dimension, but I would like to filter the years in this slicer to only the years between the current year and the last 3 years. So [Year] between [CurrYear] and [CurrYear] - 3.

As far as I can see I can only enter static values in the filter editor (i.e. [Year] between 2013 and 2016).

Is there a way to use dynamic values?

Thanks!

regards,

Michiel

[fbrossard]

Hi @bijntjede2e

You could enrich your date table by adding a calculated column for relative year

Relative Year = INT(YEAR([Date]) - YEAR(TODAY()))

and use it as filter onto your report, page or visual.and select the values according to the scope you want to analyze, in your example 0,-1,-2.

[konstantinos]

The best eway to accomplish that is in query editor since you will end up with a smaller data model.. @greggyb has a great solution in query editor that is exact what you need ( I thing for rlolling months but you can adjust it for years ) but I cannot dig up his post..

If he cannot reply I wrote a post how to create dynamic filtering in Query editor but is based on dates in other tables..i.e if you need the date dimention to contain dates that are only is transactional table..

https://medium.com/@Konstantinos_Ioannou/powerquery-dynamic-date-dimension-table-filtering-f9201cf87...