Rolling 24 months date slicer to remove scrolling

[Anonymous]

Hi all. I have a report with a Matrix visualization that is being filtered by a date Slicer with slider. There is a lot of data (46 years worth), and so there is a lot of horizontal scrolling. I want to remove the scrollbar by filtering the Matrix in such a way that we only ever see a rolling 24 months on the screen at any time, controlled by the slider on the date Slicer. Ideally we see 12 months before and 12 months after the selected date. Please see screenshot, and thank you in advance.

Slider currently shown with "Between" option

[Anonymous · Accepted solution]

Hi @Anonymous ,

I did a simple sample and you can check the results as follows.

Measure = var _s = MIN('Table'[Date])
var _max = EDATE(_s,24)
RETURN IF(MAX('Date'[Date])>=_s&&MAX('Date'[Date])<=_max,1,0)

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @Anonymous Scott,

Please see screenshot below. As you can see, I am unable to add a measure to the Columns for the Matrix. I can see that you were able to get it to work on a Line Chart and a Table, however? Please advise, and thank you for your help.

[Anonymous]

Hi @Anonymous ,

Sorry for late reply.You can use Line and stacked column chart.And set measure as Line y-axis.

Best regards,
Community Support Team_ Scott Chang

[amitchandak]

@Anonymous , check if this approach can help, I have done it for rows, you need it for columns

Throwback- Mastering Pagination in Power BI Visuals using Window Functions: A Step-by-Step Guide, How to do it- https://www.youtube.com/watch?v=a_tgxuIdM-o&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

[Anonymous]

Thanks @amitchandak. This is an intriguing possibility, but I am having trouble making it work for the matrix visualization. I'll keep trying.