do you want to be able to specify any date instead of current date? If yes, then you can use a date parameter instead of NOW() in your formula.
Here is more info on parameters: http://onlinehelp.tableausoftware.com/v7.0/pro/online/en-us/parameters.html
Thanks for the help. A parameter wouldn't be the solution I don't think I'll try and explain it a little better. I'm working on mocking up some data because it is sensitive I can't use the original data set. I want to have the Months across the columns and have a trend line where each value is a sum of the last X months. So for R3 this is what I'd want:
The easiest way is to use a table calculation, see attached workbook.
The formula here is: WINDOW_SUM(SUM([Sales]), -[X prev months], 0)
where[X prev months] is a parameter determining how many months (not counting current at the data point) are to be included in the sum. You will need to partition it appropriately for your data (Compute using...).