Cumulative Forecast

[Anonymous]

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

[YoussefBelloum]

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

[Anonymous]

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

[YoussefBelloum]

would you be able to attach some data ? some rows from your table ? some sample rows that represent your table structure ?

[Anonymous]

Certainly - please see attached.