How to add a Reference Line to a row chart in Tableau that calculates from previous time periods?

Question

I'd like to create a row chart that shows sales by product where aggregate sales is the length of the row and product is each row. The data is filtered for only the current quarter.

However, I'd like to add a reference line that shows what the average sales by product was for the previous 4 quarters.

However, after reading Tableau's documentation, it seems like this cannot be done with a parameter, but with a different procedure as stated below

You cannot select a measure that isn't currently in the view as the basis for your reference line. If you want to use such a measure, close the Add Reference Line, Band, or Box dialog box and then drag the measure from the Data pane to the Details target on the Marks card. Change the measure's aggregation if necessary. This will not change the view, but it will allow you to use that measure as the basis for your reference line. Now re-open the Add Reference Line, Band, or Box dialog box and resume at step 1, above.

However, after adding the measure from the Data pane to the Details target, the measure is being impacted by the filter and only showing the average for the current quarter. How can I have this reference line show the average of the previous four quarters?

Answer 1

First of all, you need to understand that if you have a date filter for the first quarter, you won't have the previous quarters available - because, well, your filter says that you only want the data for the first quarter.

What I would suggest is filtering the previous four quarters and adding the filter for the current quarter directly in the field (formula). The formula would be:

IF DATEPART('quarter',[Date]) = DATEPART('quarter',TODAY())
    THEN [Sales]
END

Here follows an example image from a test data source: you would only need to change from text to bar and remove the original "Sales" field (that I left so you could see the difference). After that, you could use the original field (be it as a SUM or AVG) to get the previous quarters.

Edit: Here follows another take you could have in this view. Without using reference lines, now that you have two fields you can easily use both of them in the same view using dual axis (remember to sync them).