I've attached a workbook to demonstrate how this can be done using a table calculation to lookup the value for the second to last month and add the value of the parameter. Basically, the calculation will be:
LOOKUP([Lifetime Total likes], LAST() - 1) + [Target]
It will be calculated table across (or more precisely, calculated along month). If you include that calculation in the level of detail, you can use it as a reference line.
I'd be happy to answer any questions you might have.
Next to Last Target.twbx.zip 43.0 KB
Perfect! Great sample workbook. Helped a lot.
Since my rows are actually being calculated by the Max likes in each month, I had a problem with mixing aggregated and non-aggregated values.
I fixed it by using this as the final formula:
LOOKUP(MAX([Lifetime Total likes]), LAST() - 1) + [Target: Likes]
Thanks for your help!
You're welcome. Glad to help!
1 of 1 people found this helpful
One addition for performance is to wrap the whole calc in PREVIOUS_VALUE, like PREVIOUS_VALUE(LOOKUP(, LAST() - 1) + ). That way the inner calc is only performed once for the whole partition.
Thanks Jonathan! I'll have to remember that tip!