Relative Date Filtering

[bruppert]

Hello,

I have a table that I want to be filtered on a field called "Start Date".  I want this to be a report level filter, and the options under relative date filtering are limited. What I want is a custom range, "Start Date" should be filtered to the current day - 3 and current day + 30. Best way to go about this? Thanks!

[Ashish_Mathur]

Hi,

Create 2 additional filters.  One in which you select the "Days you want to go back by" and another in which "Days you want want to move forward by".  In the first slicer, select 3 and in the next, select 30.  So in all there will be ateast 3 slicers - one is your where you select the start date and two others that i have mentioned.  Write these measure:

Chosen go back by = MIN(Table1[Go back by])

Chosen move forward by = MIN(Table2[Move forward by])

Measure1 = CALCULATE([your measure],DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-[Chosen go back by],MIN(Calendar[Date])+[Chosen move forward by]))

I have assumed that you have a Calendar Table and an active relationship from the Date column of your Data Table to the Date column of your Calendar Table.  The Start date slicer should be build from the Date column of the Calendar Table.

Hope this helps.

[MFelix]

Hi @bruppert ,

When you refer that you want to have current day -3 and current day + 30 is from today or from selected date of the user?

Do you use a calendar tble?

If this is taking into account today date one option would be to add a column that would do something like this:

Date Filter = IF(Table[Date] >= TODAY() -3 && Table[Date]<= TODAY + 30 ; 1; 0)

Then place this on your report filter and select all values that are equal to 1.

If this is based on a user selection then you probably will need to make the filter on each of the measures you use.

Can you share some more information and some mockup data as expected result?

Regards,

MFelix