8 Replies Latest reply: Jun 24, 2017 9:22 AM by Abid Peerzade RSS

    Periodic Calculation

    Abid Peerzade

      Hi All,

      I have Date and Category as dimensions and a measure and the data looks something like this

        

      DateCategoryValue
      A1/1/201210
      A5/1/201320
      A3/1/201430
      A4/1/201410
      B1/1/201320
      B6/1/201430
      B9/1/2011415
      B12/1/201440

       

       

      My objective is to calculate the previous year's value from the latest date within each category, expecting the output to be like

        

      DateCategoryValue
      A5/1/201320
      B1/1/201320

       

      I am trying the following set code

      sum( {<Year(Date) = {'$(=Year(AddYears(max(Date),-1)))'}>} Value)

      but the highlighted expression is evaluating to max of entire date column. Can we modify this code to achieve the objective or is there any other method we can adapt. Please guide me.

       

      Many Thanks,

      Abid