Date slicer max 30 days but user can switch between dates

[Anonymous]

Hi is there any way that we can use date slicer on report to allow for the user to select any range of data but only a maximum of 30 days at once?

For example if the user selected April 1 then the system would allow for them to select up to April 30.  If they selected January 1 then they could select the end range up to January 30 but user can change date within specified dates.

As i'm new to this any help will be thankfull.

[danextian]

Hi @Anonymous ,

For your use case, what I can think of is to use another table that has another  column of 0-29 days from a date.  This table can be created either in Power Query or DAX and will have a many to many relationship with the actual Dates table, the former filtering the latter.  The relationship will look like this:

And this is the result:

And here's the sample PBIX: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships 

For considerations and limitations related to using a many-to-many cardinality, please refer to this: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships 

[Anonymous]

Hi @danextian , thanks for reply but can you tell how can we read the selected start value, also only number of days are fixed(30 days) not the month.

For example if the user selected April 2 then the system would allow for them to select up to May 3.

[amitchandak]

@Anonymous , If you can use a relative date slicer.

Or you can use the rolling formula 

Rolling 30 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),30,DAY))

In case you need to display 30 days

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[Anonymous]

Hi @amitchandak , thanks for update, but is it possible that user selects only start date and end date will updated automatically . 

For example if the user selected April 1 then the system would allow for them to select up to April 30.  If they selected January 1 then they could select the end range up to January 30 but user can change date within specified dates.

Thanks