Creating a Date Slicer with a Fixed Range

[Anonymous]

For sales data, I am providing a Date Slicer in Power BI, allowing users to filter a chart based on a start and end date, using both 'real' dates (e.g. dd.mm.yyyy) and relative dates (e.g. 'day X since first sale') in different charts. Now, I am trying to somehow limit the date range, e.g. users should only be able to view ranges of up to 30 days. This could either be done by setting a start date and then choosing the range (in 'number of days') in a second field or by using a slider for dates that does not allow ranges exceeding 30 days.

Unfortunately, I am not able to create either one of these options (and I am not sure if this is even possible). Could you show me a way to achieve this?

Thank you very much for your help!

[v-yanjiang-msft]

Hi @Anonymous ,

Relative date type in slicer can only depend on the current date, but not the filtered date.

The below thread answers such problems in detail.

Required custom date Slicer Last 7 days,last 15 da... - Microsoft Power BI Community

The logic is not to put the value directly in the visual and filter it, but to write a measure to calculate value of the last 30 days based on the selected date.

As the date table has no relationship with the sales table, so the slicer with date in date table cannot filter the other visual, then based the selected date in the slicer to calculate the date 30 days ago, according to the date range, calculate the value in the measure, then put the measure in the visual.

Please feel free to let me know if you have other confusion.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your reply, I'll try following the steps in the other thread and see if I can manage to reproduce this.

[amitchandak · Accepted solution]

@Anonymous , defaulting date range for last 30 in not possible.

You need to try an option like

https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-30/m-p/1284966#M561629

[Anonymous]

Thank you, @amitchandak! Using "relative date" is definitely a step in the right direction, but I still need to use this based on a fixed date in the past.

Let me provide an example: I have different products, so I use a slicer to let users choose a specific product they want to analyse, for example product "X". In my data, I have the date when "X" went on sale and this should then be the basis for the analysis, e.g. even if I use "relative date" in my slicer, it should work based on the "on-sale date" of the chosen product.

Is there a different approach to achieve this or was this perhaps already in your suggested solution and I did not understand/apply it correctly?

[amitchandak]

@Anonymous , Prefer to use an independent date table in the slicer 

example based on what I got

measure =
var _max = maxx(filter(allselected(Table), Table[product] in selectedvalue(Product[poduct])), Table[Date])
var _min = _max -30
return
calculate(sum(Table[value]), filter('Table', Table[date] <=_max && Table[date] >=_min ))

if product is from joined table or same table

measure =
var _max = maxx(filter(allselected(Table), Table[product] in selectedvalue(Product[poduct])), Table[Date])
var _min = _max -30
return
calculate(sum(Table[value]), filter(all('Table'), Table[date] <=_max && Table[date] >=_min ))

[Anonymous]

Thanks again, @amitchandak. I have already used the method of creating an independent table, adding a measure and then generating a custom slicer for a different problem, so I know the general goal, but I am not able to wrap my head around the process in this case, since I am fairly new to Power BI. Do you perhaps have a link etc. for a step-by-step description?