Linear Trend line for subset of dimension

[nagaiank]

I have a need to display 12-month sales trend. When all the months have sales amounts, the trend line displays correctly. If the sales amounts are available for a few months only, the default trend line display does not seem to be correct as seen below. How can I set chart properties so that the trend line shows only for the range of months with sales amount? In the following example, I would like to display trend for the last five months only.

An application with sample data is attached. Thank you for your help.

[swuehl · Accepted solution]

I don't think there is an option for the linear Trendline option on expression tab to do what you want.

You can look into the linest_m and linest_b function to create your own linear trend, maybe something like

=LINEST_M({<Month = {"=Sum(Region2Sales)>0"}>}TOTAL Aggr(Sum(Region2Sales),Month) ,aggr(Month,Month)) *Only(Month)

+LINEST_B({<Month = {"=Sum(Region2Sales)>0"}>}TOTAL Aggr(Sum(Region2Sales),Month) , Aggr(Month,Month))

[nagaiank]

Thank you  Stefan. This is what I was looking for.