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.
You could do this in a very pre-planned, 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.
Prior Period LOD.twbx 347.7 KB
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 left-joining your datasource to itself
on a calculated field with Period-1 on side and Period on the other (screenshot below)?
Please see workbook v10.5 attached in the Forum Thread:
287935prev.twbx 23.6 KB
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.