Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculate dynamic %change based on date slicer

Hi All,

 

We having Total sales Column in the database and need to find a %change in sales on the period selected.

 

I solve this using the following calculation,

This year Sale = calculate(sum([total sale),filter(Item,Item[Timestamp].[Year]=2019))

Last Year Sale = CALCULATE(SUM([total sale]),SAMEPERIODLASTYEAR(Item[Timestamp].[Date]))

 

But I need to find the dynamic %change, which depends on Date slicers selection, we use Relative Date slicer in the report,

So if we select last 1 Week on date slicer -> %change if for this selected week with last week,

if we select last 1 Month on date slicer -> %change if for this selected Month with last Month, and so on.

 

 

Please Help,

 

Thanks in Advance.

 

%

2 Replies

  • nhoward's avatar
    nhoward
    Icon for Resolver I rankResolver I
    6 years ago

    HI Anonymous 

     

    Are you using a Date table?  or just the date column of your item table. 

     

    In this instance (and almost always) a master calendar table will make the process much easier. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi nhoward,

       

      Thank you for the quick response and your precious time,

       

      Yes I am using the date column of the item table, and thanks for your suggestion to use separate date table, but can you please provide the measures used for to calculate %change for dates selected in Relate Date Slicer

       

      Thanks