5 Replies Latest reply on Aug 1, 2013 7:42 AM by Joshua Milligan

# Target (reference line) and parameter based on second last value

Hey All,

If I have a target of '20 likes per month', I want the reference line to add 20 to the previous month's total.

In the example below, if the likes per month is 20 (in the parameter), I want the reference line to be at May's Total plus to 20.

I.e.  278 (258 + 20)

How can I make the reference line show 20 more than the second last month?

Thanks all!

• ###### 1. Re: Target (reference line) and parameter based on second last value

Louis,

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.

Regards,

Joshua

• ###### 2. Re: Target (reference line) and parameter based on second last value

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]

• ###### 4. Re: Target (reference line) and parameter based on second last value

Hi Josh,

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.

Jonathan

1 of 1 people found this helpful
• ###### 5. Re: Target (reference line) and parameter based on second last value

Thanks Jonathan!  I'll have to remember that tip!