I believe the trick to getting this output is to create a table calc which you'll nest in another table calc. This will allow you to have 2 different compute settings which was required (at least by me) to produce the view you are looking for. I've attached a twbx which I think you can follow but I'm going to provide a brief explanation of what I did.
1. Create a lookup field for last years sales. Lookup Sales - 1: LOOKUP(SUM([Sales]),-1)
2. Create your running SUM. LY Running Sales: RUNNING_SUM([Lookup Sales -1])
3. Drag you measure to the view and set the following compute settings.
Let me know if you have any questions.
Last Year Rolling.twbx 1.2 MB
Hi Ivan! Thanks for your reply.
So your "two calculations" method is one that I've been working through the past few days, but am getting stuck when applying this method to the intended table format (i.e. days of month along the columns). Your example works with a default "Table (across)" calculation on Sum(Sales), but when I need a non-default calculation on Sales like "Table (down)", the second calculation for Running Sales falls apart because it is calculating with the wrong method.
When I try to edit the Sales default calculation, I can't seem to force it to Table (down), even when fiddling with the Advanced options. I think the main reason for this is the inability to select the granularity of Date (i.e. Month(Date) or Year(Date) as opposed to just [Date])
So the main question now is this: How do I force a table calculation's "compute using" setting to be an inherent property of the calculation, so that a secondary calculation that references the first will use the correct setting?
Please let me know if I can clarify anything I've said thus far. Thanks!
I'm not sure I'm entirely clear on what you are trying to do. I don't often use the Default Table Calculation settings, I generally keep that setting at automatic and do my compute once the table calc has been added to the view. If you set the default table calc to compute on a dimension that is not in the view it won't work anyway.
Attached is a copy that shows last years running sum by day of month in columns. If you are trying to get a different output let me know.
After some fiddling I got the first answer to work for me! Thanks for your help, Ivan.