1 Reply Latest reply on Jun 9, 2017 7:49 PM by Wilson Po

    Sum data from row with max date

    Matthew Perry

      Hi Everybody,


      I'm embarrassed to say that I've spent way too many hours trying to solve this - I suspect I may be over complicating things.


      My GOAL - calculate the following metrics so I can use them on multiple dashboards.

      Members (as at 2016):


      Prev yr members:


      Variance to prev year:



      The trick is that Members is not always calculated from the current year. It is based on the max year in the data, and previous yr members is relative to that.

      (in the sample, current members is the sum of membership in 2016).


      I’ve tried Window_max calcs but seem to be going around in circles.


      I'm sure Im overcomplicating it - any help much appreciated.




        • 1. Re: Sum data from row with max date
          Wilson Po

          Hi Matt,

          I think you are missing is just the ability to compute the max year based on the data available in the data set - a FIXED Level of Detail Expressions can help with that:


          This will always result in the max year that is found in the data. In the sample workbook you included, this is 2017. We can then build separate calculations off this by comparing this new measure to the [Year] Dimension:

          • [Members in Max Year] = IF [Year]=[Max Year] THEN [Total members] END
          • [Members in Previous Year] = IF [Year]=[Max Year]-1 THEN [Total members] END
          • [Variance to Prev Year] = SUM([Members in Max Year])-SUM([Members in Previous Year])

          I've attached the original workbook with the solution.