topic Re: Cumulative Forecast in App Development

Cumulative Forecast

Anonymous — Mon, 05 Mar 2018 22:17:32 GMT

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

YoussefBelloum — Mon, 05 Mar 2018 23:41:54 GMT

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

Anonymous — Mon, 12 Mar 2018 22:21:14 GMT

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

Re: Cumulative Forecast

YoussefBelloum — Wed, 14 Mar 2018 16:46:22 GMT

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

Re: Cumulative Forecast

Anonymous — Wed, 14 Mar 2018 17:46:54 GMT

Certainly - please see attached.