Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate variation based on a dynamic date filter

Hello, 

 

I´m trying to calculate absolute and percentual variations on my visualizations (scorecards and graphs) based on a dynamic filter date.

Let´s say if I choose the last 2 weeks/months on my filter, I want it to be compared to the previous 2 weeks/months.

 

Example:

 

 

 

 

 

 

Is that possible?

Thanks in advance!

Need Help
Show and Tell

5 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous,

    the below measure should be a good start, it will shift days in current selection ei. looking at weeks it will ofset 7 days.

    If you need something that offsets months where the count of days differs its still possible. 

    Time Shift =
    VAR e = FIRSTDATE('Calendar'[Date]) -1
    VAR s = e + DATEDIFF(LASTDATE('Calendar'[Date]) -1, e, DAY)
    RETURN
    CALCULATE(
    Sales[sales],
    FILTER(
    ALL('Calendar'),
    'Calendar'[Date] >= s && 'Calendar'[Date] <= e
    )
    )
    Hope This Helps 
    Mariusz
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Mariusz,

       

      First, thanks a lot for your advice.

       

      I´ve created the measure "Previous period" as it is below:

       

       

      Then I tried the solution you suggested but I´m still running into a problem: after creating the measure I´ve set the date filter for 5 days but it doesn´t show the previous 5 days. Instead it´s just showing the last day for each of the day:

       

      I guess I´m doing something wrong.

       

      Could you help me out on that?

       

      Thanks again!

      • Mariusz's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi Anonymous 

        By Adding Date in your table you are applying a filter context so on one day it will offet only one day back, if you look at the totals it will give you your 5 days.

        Let me know if I ansered you question, if not please reiterate your requierment.

         

        Mariusz