Relative Date Filter with Explicit Date Range Option

[GabrielSantos]

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.

[Stachu]

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

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers#sync-and-use-slicer...