Trend and forecast lines in charts

We will start by adding a trend line to the lower chart, Monthly Sales and Margin. Then, we will add a forecasted trend line out 3 months to the upper chart, with a 12-month rolling average.

Adding a trend line to the monthly sales and margin chart

1. Right-click on the chart titled Monthly Sales and Margin and choose Properties:

2. Navigate to the Dimensions tab.

3. Click on the Add button to add a new dimension.

4. On the right-hand side in the Label box, type the word Trend.

5. Next, click on the ellipses to enter the formula for our trend line:

   if(v12month<=month(today()),sum({<YYYYMM ={">$(v12month)"}>}[Sales Amount]),avg(total aggr(if(v12month<=month(today()),sum([Sales Amount])),v12month)))

   Our formula says that, if the variable in v12month is less than the current month, then use the sum of the data we have in Sales Amount. If we don't have the current month's data, then get an average of the aggregate of the last 12 months of data:

6. Make sure that the checkbox...