4 Replies Latest reply: Mar 14, 2018 1:46 PM by conor cliffe RSS

    Cumulative Forecast

    conor cliffe



      I am trying to create a cumulative sum forecast trend line but I am struggling with the syntax of linest_m and linest_b



      RangeSum(Above(TOTAL Sum({<[Data Source] = {'Bookings'},[Date.autoCalendar.InYTD]={1}>}[Instrument Quantity]),0,RowNo(TOTAL)))





      The part, in particular, that is proving to be tricky is making the linest_m portion of the equation be a cumulative sum.  I am hoping that I can handle this outside of the script.  Thank you in advance for your help.


      Best regards,


        • Re: Cumulative Forecast
          youssef belloum



          why did you put TOTAL after the sum and inside Rowno() ?


          Try this:


          RangeSum(Above(Sum({<[Data Source] = {'Bookings'},[Date.autoCalendar.InYTD]={1}>}[Instrument Quantity]),0,RowNo() ))

            • Re: Cumulative Forecast
              conor cliffe

              So the 'TOTAL' is for the cumulative piece.


              Here is my full formula, which still does not capture the cumulative component:




              linest_m(total aggr(if(Sum({<[Data Source] = {'Bookings'},[Date.autoCalendar.InYTD]={1}>}[Instrument Quantity]),

              Sum({<[Data Source] = {'Bookings'},[Date.autoCalendar.InYTD]={1}>}[Instrument Quantity])),


              only({1}[Date.autoCalendar.Date])+linest_b(total aggr(if(Sum({<[Data Source] = {'Bookings'},[Date.autoCalendar.InYTD]={1}>}[Instrument Quantity]),

              Sum({<[Data Source] = {'Bookings'},[Date.autoCalendar.InYTD]={1}>}[Instrument Quantity])),[Date.autoCalendar.Date]),[Date.autoCalendar.Date])