6 Replies Latest reply on Nov 5, 2018 6:21 AM by Andrew Lloyd

    Displaying the Year over Year % change in revenue for a given Month

    Andrew Lloyd

      I have some data down to a pretty granular level. I am trying to display a single number for a dashboard: The Year-Over-Year change in revenue for the current month. Pseudocode is: (sum(10/2018 revenue)-sum(10/2017 Revenue))/sum(10/2017 Revenue). I also did this for the overall Year-to-Date difference using some simple LODs:

      (SUM(CASE [Fiscal Year] WHEN {FIXED:MAX([Fiscal Year])} then [Adjusted GSAR FullWeeks] END) - SUM(CASE [Fiscal Year] WHEN {FIXED:MAX([Fiscal Year])}-1 then [Adjusted GSAR FullWeeks] END))

      /SUM(CASE [Fiscal Year] WHEN {FIXED:MAX([Fiscal Year])}-1 then [Adjusted GSAR FullWeeks] END)


      But adding in the month is giving me issues, because I need to use the same month year-over-year. Using {FIXED:MAX([Fiscal Month])} won't work b/c the max fiscal month is 12 (because we have month 12 data for 2017). I instead need the max fiscal month for the most recent year, and to use that month for both the most recent year and the most recent year-1. Is there a way to lock the LOD to the most recent year? Or maybe there is a better way all together?