Relative date slicer

[ashwinkolte]

Hi

I have a relative date slicer. Something like this which we all know

I want to calculate variance of measure (lets says sum of sales) from  equivalent previous period .

For e.g 

If the user selects "Last 7 days" in the slicer then I want to calculate variance from last 7 days prior to the selected "Last 7 days" of the slicer . I understand for this we can use selected value measure and find max and min dates of the slicer and use it . The challenge is here -  

If the user selects "Last 1 Calender month" in the slicer then I want variance from its previous calender month . E.g if the last calender month is May 24 , then I want variance from Apr 24 , although the number of days in April and May  differ . I want to identify what level the slicer is i.e day , week, month, year level and then calculate from its  previous period . I dont want to calcuate 31 days of May month  and go back 31 days from 30 April. 

Hope I am able to articulate the question . 

Any help will be greatly appreciated  

[lbendlin]

Use a disconnected dates table and the filter pane. It has all these advanced filtering options already available.

If you want PPTD (previous period to date) then add an additional filter of "is before today in PP".  Ideally you have that precomputed in the calendar table and the calendar table is in import mode and refreshed daily.

[ashwinkolte]

Hi @lbendlin   .. Thanks for responding but I didnt quite understand what is disconnected date table. Please elaborate 

Basically , here is my requirement - If the user selectes past  3 calender months using the given slicer  (which translates to say Jun,May,Apr) , then I have to calculate variance from prior three months i.e Mar,Feb,Jan. Or if user selects 7 days then I have to calculate variance from 7 days prior to these selected 7 days . What is the best way to achieve this ?  The user may select any number of days or months or even years uing the relative date slicer