1 2 3 Previous Next 38 Replies Latest reply on Jul 18, 2018 6:55 AM by Shinichiro Murakami

    Cumulative sum calculated field

    Vishal D

      I have below fields in the datasource.

       

      [Report Year] = [2016, 2015, 2014, 2013, 2012]

      [Report Month] = [1,2,3,4,5,6,7,8,9,10,11,12]

      [Order Date] = different dates

      [Profit] = [123, 456, 789] etc..

       

      I want to put a where clause (filter) such that.

       

      I want to do a max on 'Order Date' then take the month out of it. calculate previous month from it.

       

      Pass cumulative months i.e from Jan to previous calculated month to [Report Month] if the [Report Year] = Current Year else Pass [Report Month] = Jan to Dec

       

      e.g Max of Order Date 9/25/2016 then Month = 09 then previous month = 08. So now pass Report Month = 01 to 08 cumulative (fetch Profit for 2016 only for 8 months )  if report year = current year which is 2016 else pass Report Month = 01 to 12 cumulative (fetch Profit for prior years only for 12 months)

       

      If this is unclear I will try putting dummy data and put a twbx

        1 2 3 Previous Next