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 Mont...
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