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.
Right-click on the chart titled Monthly Sales and Margin and choose Properties:
Navigate to the Dimensions tab.
Click on the Add button to add a new dimension.
On the right-hand side in the Label box, type the word
Trend.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
v12monthis less than the current month, then use the sum of the data we have inSales 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:Make sure that the checkbox...