
1. Re: Prior Period LOD Calculation
Paul Wachtler Nov 16, 2018 3:56 PM (in response to Keith McCarthy)LODs don't work like that. They move your calculations up or down a level of detail, but they do not access records across tables.

2. Re: Prior Period LOD Calculation
Jennifer VonHagel Nov 16, 2018 5:28 PM (in response to Keith McCarthy)Hi Keith,
You could do this in a very preplanned, brute force kind of way. Check out the attached workbook; my calculations are in the folders highlighted below.
I've put all the interim calculations as their own separate fields we can clearly see what's going on. An LOD can return a specific year's sales as offset from an anchor year, but the LOD can only return ONE year's sales. To get more years, you need more LODs. The final Prior Period Sales column can layer the LOD calculations so everything shows in a single column, but you have to create the specific number of LOD Year Sales calculations as prior periods you want to show.
The anchor year I've used here is in the parameter "Current Year". You don't have to use a parameter, you could use an LOD to find the max year in the dataset instead and that would also work.
The workbook is 10.5, let me know if you need an earlier version.
Best,
Jennifer

Prior Period LOD.twbx 347.7 KB


3. Re: Prior Period LOD Calculation
swaroop.gantela Nov 16, 2018 6:09 PM (in response to Keith McCarthy)Keith,
I think Paul's right. Rather than ignoring the current period, the EXCLUDE is
ignoring the entire dimension of [Period] and moving up a level.
Alternatively, how would you feel about leftjoining your datasource to itself
on a calculated field with Period1 on side and Period on the other (screenshot below)?
Please see workbook v10.5 attached in the Forum Thread:

287935prev.twbx 23.6 KB


4. Re: Prior Period LOD Calculation
Keith McCarthy Nov 19, 2018 2:53 PM (in response to swaroop.gantela)Thank you to everyone who replied. All the answers were correct in their own way, but Swaroop's answer is an elegant solution. Thank you again.