Relative Dates

[Stuartw1988]

Hi,

I am using filters to establish list of customers who have certain last order dates.  I have used relative date feature to get customers who have orders in last 30 days - but how can I use this for customers between (for example) 31-90 days?

I'm currently manually inputting last order date each time I refresh the model, which is useable given I have a small number of cohorts, but not ideal.

Thanks,

[v-xiaotang · Accepted solution]

Hi @Stuartw1988 

You can try this method

(1) create the table below, use it in slicer

(2) create the measure, use it to filter table

filter = IF(MIN('FactTable'[Start Date])<=SlicerDays[SlicerDate1] && MIN('FactTable'[Start Date])>=[SlicerDate2],1,0)

you can put it in visual level filter,

For more please check the sample file attached below.

Best Regards,

Community Support Team _Tang

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

[amitchandak]

@Stuartw1988 , 

Rolling 31-90 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today()) -31
var _min = _max -90
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

or

Rolling 31- 90 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ])-31,-60,DAY))