2 Replies Latest reply on Jul 10, 2018 11:22 PM by kumar bharat

    Running Sum that resets monthly based combined with another measure

    Daniel Cavenagh

      Hi all,

       

      I have a bit of a tricky problem that I'm sure is achievable by someone smarter than me.

       

      I have some forecast cash flow data from a variety of teams as well as the actual cash flow moment and the actual closing balance.

       

      What I want to do is create another field with the 'Forecast Closing Balance' that is a cumulative sum of the forecasts movements added to the opening balance of the month (i.e. the closing balance of the last day of last month).

       

      But I need the calculation to reset each month. So in February it would start with the closing balance of the last day in January and then cumulative sum the forecast movements on top of that. Then on the first day of March it would start again, the actual closing balance of the last day of February and then the cumulative sum of the forecast movements on top of that. (See table below if that helps visualise the task).

       

      To be honest I haven't even really got close with my own calculation attempts.

       

      Unfortunately I can't change the source data.

       

      I will appreciate any help on this.

       

      Cheers,

       

      Daniel.

       

      Tableau 10.5.2 by the way.

       

            

      TeamDateForecast MovementActual MovementActual CloseForecast Closing Balance
      A31/01/201684-6788191
      A1/02/2016-46765888497724
      A2/02/2016-21859694457506
      A3/02/2016-68315896036823
      A4/02/201646-37692276869
      A5/02/20163824092677251
      A6/02/20169251597827343
      A7/02/2016843762105448186
      A8/02/2016948-60199439134
      A9/02/2016-424971109148710
      A10/02/2016734680115949444
      A11/02/20166055541214810049
      A12/02/20163062221237010355
      A13/02/20166015191288910956
      A14/02/20161475931348211103
      A15/02/20162924211390311395
      A16/02/201621-1331377011416
      A17/02/20169265781434812342
      A18/02/20168622521460013204