Forum Discussion
Relative Date Filter with Explicit Date Range Option
I would like to accomplish the following:
1. The report defaults to YTD (in an ideal world, you would also be able to have a dropdown to select common business dates such as Same Period Last Month, YTD, MTD, etc.)
2. The user has the ability to manually enter any explicit date range, such as 1/1/2018 - 1/3/2019, 2/1/2019 - 3/5/2019, etc.
3. The explicit date range selection will carry over to other pages of the report.
Here's what I've tried:
1. Relative date filtering. This doesn't work becuase there is no way to explicitly enter a date range. It is impossible to select 2/1/2019 - 3/5/2019 because the relative date filtering is always relative to today.
1 Reply
Ad. 1) create a table 'Periods' like this:
Period MTD YTD LY YTD LY and a measure like this:
Measure = VAR __Period = SELECTEDVALUE(Periods[Period]) VAR __MTD = DATESMTD('Calendar'[Date]) VAR __YTD = DATESYTD('Calendar'[Date]) VAR __YTD_LY = SAMEPERIODLASTYEAR(__YTD) VAR __LY = SAMEPERIODLASTYEAR('Calendar'[Date]) RETURN SWITCH(
__Period, "MTD", CALCULATE([SumOfValue],__MTD), "YTD", CALCULATE([SumOfValue],__YTD), "YTD LY", CALCULATE([SumOfValue], __YTD_LY), "LY", CALCULATE([SumOfValue], __LY), BLANK() )adjust the table and measure names accordingly
it will always calculate the values depending on the filter context in the calendar table, see here:
Ad.2 just create a regular slicer based on your Calendar table - it will provide the reference point for all the MTD/YTD calculations, i.e. if you select 201907 in this slicer the visual above would only return single row.
With multiple months selected (e.g. 201905-201907) the MTD/YTD/LY calculations get a bit counterintuitive, so I would really think on what you want to present and how
Ad.3 you have to use synced slicers
