Think about the scenario where you want to calculate the difference e.g. in Sales between current and previous months, in other words calculate a Revenue gain from month to month. The first thing that probably comes to mind is let’s use Previous() function to get these value. In some circumstance it works just fine. In the following eFashion data set we have 2 Lines values in a section, a Date and Sales revenue. With Previous() function we easily calculated prior month Revenue values and came up with the final formula for calculating a Revenue Gain.
=[Sales revenue]/Previous([Sales revenue])-1
The challenge starts when you want to reverse the sort on dates to a descending sort, first show the latest date. Previous() function unfortunately has no option for a parameter which would reverse the order that function pulls data in. There is however a different function RelativeValue() which can do it but also has some limitations. Let’s use the same data set and next to Previous() function we will show RelativeValue() results.
Here is the syntax of our function:
input_type RelativeValue(measure | detail;slicing dims;offset) in our case we will use the following formula:
= RelativeValue([Sales revenue];([Date]);1)
We can see that although most of the values are correct there are some breaks in data like e.g. for Leather on 9/1/05.
What is actually going on in here?
RelativeValue() function goes through all report values of a given dimension disregarding the fact that we have a section on Lines and it should calculate Sales Revenue for dates in each Line separately. When we look at the following table we see that there is one gap in dates for Leather and 2 gaps for City Skirts. That’s why when 80 is looking for a previous value of Sales Revenue for Leather on 9/1/05 it finds a null value because the previous value of a date in the whole report is 8/1/05.
How to resolve the issue? We can either create a separate querie for each Line or apply a prompt on it. When we bring in only one Line value in to the report the calculation is correct.