2 Replies Latest reply on Aug 24, 2016 4:12 AM by Emily Brown

    Returning a % difference across years whilst data filtered to one year only

    Emily Brown

      Hi All,

       

      I'm building a dashboard which looks at comparative cost elements across years (and many geographical sites). The dashboard is limited to show one year at a time (due to the complexity of the other factors inc. geographical locations) by using a date filter which is applied to all sheets from the main dashboard page. For each year there are around 100 measures in 6 main heirarchical groups - pay costs, non-pay costs, buildings & utilities costs etc. I would like to return a % Change from the previous year on the same sheet but I am really struggling - I have tried a [previous year] filter as well as LOD calcs and a parameter but it's just not working.

       

      Ideally my sheet would look like this:

       

      Cost ElementCost% Change from previous year
      Pay Costs£1,090,428+1.2%
      Non Pay Costs£2,116,999-0.8%
      Building & Utilities£503,294+12.7%
      Other Costs£225,099+0.2%

       

      What I'm aiming for - On the Dashboard if you select (for instance) 2014/15 you will see all of the costs for that financial year and also the % change from 2013/14, then if you select 2012/13 on the dashboard and this would change to show the 2012/13 costs and the % change from 2011/12. - Sounds simple but it's proving tricky.

       

      Currently I have managed to create a seperate sheet with the last 3 years change %, but I can't get it on my main sheet. I'd like it to be the same page as the Cost Elements are the first level in a heirarchy and I'd like to be able to expand to look at the sub-costs groups and have the %change apply to them too.

       

      My data is too sensitive to publish here but I can try & whip up a dummy version if that helps.

       

      Thanks in advance,

       

      Emily