title: Grouping a set of revolving years (Reporting Services 2016)
body: <p>Long time Stackoverflow user, but never actually asked questions. I'm currently stumped on a report request.</p>
  <p>What I need to do with Report Builder is displaying data on pages grouped by three years, but here's the catch: I need it to be revolving.</p>
  <p>I put together a query, but it's static:</p>
  <pre><code>SELECT distinct *, (dense_rank() over (order by [Year]) - 1) / 3 + 1 as [group]
  FROM dwmy
  </code></pre>
  <p>It works fine for grouping years when I don't also need to still display past years. To give you an idea of what I'm trying to accomplish.</p>
  <p>So, in the current year, it would look like:</p>
  <p><img src="https://i.stack.imgur.com/8EWyQ.png" alt="2019, 2020, 2021" /></p>
  <p>In 2022:</p>
  <p><img src="https://i.stack.imgur.com/08YWd.png" alt="2020, 2021, 2022" /></p>
  <p>In 2023:</p>
  <p><img src="https://i.stack.imgur.com/rCT0V.png" alt="2021, 2022, 2023" /></p>
  <p>As you can see, the current year still stays within the first group, but as a new year starts, it slowly works its way off the first page. The old groupings would still be on older pages, so users could still see the old groupings of 2019, 2020, 2021 or 2020, 2021, 2022.</p>
  <p><img src="https://i.stack.imgur.com/jEFXj.png" alt="2019, 2020, 2021" />
  <img src="https://i.stack.imgur.com/LY7YZ.png" alt="2020, 2021, 2022" />
  <img src="https://i.stack.imgur.com/lMWAz.png" alt="2021, 2022, 2023" /></p>