excelexcel-formularateslicers

Growth Rates by custom time period in excel

I have sales data for TV category for 24 months with columns like

DATE    Region    Brand   SKU     SALES     
Jan19    North   SAMSUNG  138358  1000  
Feb19    North     LG     118074  100
Jan20    South     TCL    142740  2000  
Feb20    West    SAMSUNG  144591  150
Dec20    West    SAMSUNG  111111  200

I am setting up 2 slicers/filters for the "DATE" columns - "BASE DATE" and "CURRENT DATE". In the backend, there are two pivots, one for each base date and current date. However, I am not able to calculate the custom period growth rate. For example, if the user filters BASE DATE as Jan 19 and CURRENT DATE as Dec 20, I should be able to calculate the growth rate like [(Dec20-Jan19)/(Jan19)]*100.

Is there any way to do it? Even using a macro?

Solution

  • Is this what you need?

    Result picture

    In C12 there is a formula =(E9-B9)/B9 or you can also have =E9/B9-1 with 'Percentage' as a cell format.