cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GabrielSantos Regular Visitor
Regular Visitor

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 1
Super User II
Super User II

Re: Relative Date Filter with Explicit Date Range Option

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:

Capture.PNG

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

 

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors