4 Replies Latest reply on Oct 14, 2018 7:42 PM by ShivaRam Chennapragada

    Variance % Against Last Year

    Loretta Antonino

      Hi Everyone,

       

      I am trying to replicate the below graphs that were created in Excel. The purpose is to compare sales data by category against last year. I have the year dimension that has the 2 year values, but I don't know how to create a calculation to see the % variance from last year to this year (bottom graph). I can easily reproduce to the top graph, by just plotting the year against the sales $ measure. But I don't know how to product a calculation when the data data is stored in a single dimension.

       

      For a moment I thought I could create a calculation that said if year = 2018, then give me the sales and a second calc that said if the year = 2017... Then in a third calc, figure out the % variance based on the two previously created measures. But I don't want to hard code the years, so that is a hurdle I need to jump. Our Financial Years also don't follow calendar years, with the year running from July to June, with KPI reporting produced one month behind (after sales are finalized), so I can't just look at today's date and compare it to last years.

       

      Example below Cat 1 sales are 14% less than the year prior with $76.6K less in sales.

       

      TY vs LY.JPG

       

      Anyway I hope that all makes sense and someone is able to point me in the right direction.

       

      Thank you for your time.