Dynamic date range

[Seanan]

Hi guys,

I have a bit of an issue here which I've been struggling with.

I have a slicer in my report that currently has a date range that is 01/08/2022-Today() based on dates from a date table. I need this slicer to be dynamic so that it starts on 01/08 but the year should remain 2022 until 01-08-2023 to which a 'new academic year' has started.

So essentially until the 31st of July 2023 the date range will remain 01/08/2022-Today(), but once it's 01/08/2023 the slicer will dynamicly update to start at that range (This will need to continue for 2024, 2025, 2026, etc).

Any ideas on how to achieve this?

[jdbuchanan71 · Accepted solution]

@Seanan 

Could you add a column to your Dates table like this?

Current Range = 
VAR _Today = TODAY()
VAR _Year = IF ( MONTH ( _Today ) < 8, YEAR ( _Today )  - 1, YEAR ( _Today ) )
VAR _Start = DATE ( _Year, 8, 1 )
VAR _End = EOMONTH ( _Start, 11 )
RETURN [Date] >= _Start && [Date] <= _Today

Then you set a filter on 'Dates'[Current Range] = True on either the slicer or the page.

[Seanan]

Hi @jdbuchanan71 

That's exactly what I'm looking for.

Thank you for your swift solution.