Forum Discussion
Dynamic date range
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?
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] <= _TodayThen you set a filter on 'Dates'[Current Range] = True on either the slicer or the page.
2 Replies
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] <= _TodayThen you set a filter on 'Dates'[Current Range] = True on either the slicer or the page.
