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

    Cumulative Forecast

    conor cliffe

      Hello,

       

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

       

      Measure:

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

       

      Dimension:

      [Date.autoCalendar.Date]

       

      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,

      Conor

        • Re: Cumulative Forecast
          Youssef Belloum

          Hi,

           

          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])),


              [Date.autoCalendar.Date]),[Date.autoCalendar.Date])*


              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])