
1. Re: How to lock a calculation result
Naveen Agarwal Jul 17, 2016 12:25 PM (in response to Ray Yu)Hi Ray
Looks like you want to use the Dec 2015 sales as a fixed reference value, which needs to be subtracted from sales of each of the following months.
As long as the reference month (in this case Dec 2015) is the First month/year in your dataset, the approach I took will work. You can get a graph like this:
I created a Table Calculation:
Reference Sales = lookup(sum([Sales]),first())
Then I subtracted this number from each of the other values
Change in Sales = sum([Sales])[Reference Sales]
You can now have a graph of Change in Sales by Month/Year.
Does this work?

2. Re: How to lock a calculation result
Ray Yu Jul 18, 2016 7:21 AM (in response to Naveen Agarwal)Hi Naveen,
Thanks for your help. Unfortunately Dec 2015 is not the first month in the data. I have 2 year, 24 months' data. So first() won't apply here.
Thanks,
Ray

3. Re: How to lock a calculation result
Naveen Agarwal Jul 18, 2016 7:27 AM (in response to Ray Yu)Do you want to compare sales to Dec 2015 moving forward only (i.e. in 2016) or do you also want to do this backwards? If you only want to compare 2016 sales, then you could apply a filter to select only Dec 2015 onward. Then you could use this technique.
If not, then you need to setup a calculation with a constant offset equal to Dec 2015 sales and apply to all data.

4. Re: How to lock a calculation result
Ray Yu Jul 18, 2016 12:32 PM (in response to Naveen Agarwal)Hi Naveen,
Thanks for your advice. Although it's not perfect, I will apply a filter to only select Dec 2015 onward.
Thanks,
Ray

5. Re: How to lock a calculation result
swaroop.gantela Jul 18, 2016 4:36 PM (in response to Ray Yu)Ray, Naveen,
Apologies for jumping in.
I may not have caught all the details,
but I was wondering if you could use a Level of Detail calculation to
get the value of the Dec2015 sales and fix it for all values, and then
you could make the subtraction for every month:
{FIXED : SUM(
IF [Order Date Parameter]=YEAR([Order Date])
AND MONTH([Order Date])=12
THEN (Sales)
END
)}
You could then subtract off this amount from the SUM(Sales) of other months:
SUM(Sales)SUM([Dec2012Sales])

211272dec.twbx 421.6 KB


6. Re: How to lock a calculation result
Ray Yu Jul 19, 2016 7:09 AM (in response to swaroop.gantela)Hi Swaroop,
Thanks for your help! This is exactly what I was looking for!
A good learning of level of details calc use
Thanks,
Ray