topic Re: Relative date slicer in DAX Commands and Tips

Relative date slicer

ashwinkolte — Sat, 22 Jun 2024 06:58:08 GMT

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

Re: Relative date slicer

lbendlin — Sat, 22 Jun 2024 13:27:00 GMT

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.

Re: Relative date slicer

ashwinkolte — Thu, 27 Jun 2024 17:53:33 GMT

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