Default date - slicer

Power BI
1

Hello to all, wanted to see if anyone here came across this requirement of being able to have a default start date in the date slicer?

The ask in my case is to have the date slicer default to rolling 3 months (precisely default start date to be 2 completed month prior from current date ) like shown in the image below,
image
So, suppose we are on June 5th , the slicer should start from 4/1/2020 until 6/5/2020…

However, the other aspect of this requirement is that users also need to have the option to be able to go back in time i.e be able to use slicer to see data for previous months outside of the default selected date range, which is what makes it more complicated, else i could use relative date features…

thanks for reading my message and appreciate any suggestions…

regards

1 Like
2

Hi Santhosh,

This is very interesting task, could you pls attached the file?

3

Hello @sanappi09,

You might use Relative Date Filter from the Filter Pane and filter everything (on a page or on the entire report) filtered as per your needs.

I made a GIF file for a sample model: check it below.
I hope this helps.

Relative Date Filtering
Relative Date Filtering1919×965 1.66 MB

If you liked my solution please give it a thumbs up :+1:.

If I did answer your question please mark my post as a solution :white_check_mark:.

Thank you !

Cristian

1 Like
4

Hi @sanappi09,

Only thing I can think of is placing a filter on your Report page…

First add a Calculated Column to the Dates table for the Rolling 3M Period

Rolling 3M =
VAR CurDate = CALCULATE( MAX( Dates[Date] ), FILTER( ALL(Dates), Dates[IsAfterToday] = FALSE() ))
VAR FirstDay = DATE( YEAR(CurDate), MONTH(CurDate)-2, 1)
RETURN

IF( Dates[Date] <= CurDate && Dates[Date] >= FirstDay, TRUE(), FALSE() )

When Rolling 3M is placed in the Page level Filters section
image

And when Rolling 3M is toggled off
( here you also need the IsAfterToday = FLASE otherwise dates will project forward )
image

Downside your users will have to enable/disable this via the Filter Pane…

ALTERNATIVE
You could achieve the same without adding a Calculated Column by using MonthOffset.
image

But again to also influence the visible dates in the Slicer, you’ll have to use the Filter Pane.

I hope this is helpful. Here’s my sample file.
eDNA - Rolling Month Slicer.pbix (450.7 KB)

3 Likes
5

thanks Cristian!

Relative date feature is helpful but it does not help address the other requirement of the user, which is to be able to see the data outside of the selected time period selection

6

thanks Melissa, i will try to use your dax in conjunction with a bookmark, which might provide the solution i am looking for… much appreciate your help!

7

@sanappi09
I realize you said that a relative date filter won’t work - but I feel that I have to remind everyone that it’s possible to have a relative filter on the report page. This would allow your end-user to change the dates forwards or back as they see fit:

image

the tricky part, and possibly the problem you have now that I think of it, is that you could not continuously capture a 3 month period, if your user didn’t want to include the 2 most recent months in the slicer, then relative date won’t work.
But, if you are willing to take some additional space on the page, that could be worked around - it would just be very clunky to have two date slicers on the same page…

image
image436×544 63.3 KB

2 Likes
8

Sam has explained time intelligence functions very well. This video will give you good insights.

9

image
image838×49 25.9 KB

10

Hi @sanappi09, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!