First a question - Why are you avoiding "Table Calculations" - lookup is a table calculation that navigates around the underlying table for the viz - if you want to look at last year you will be using some table calculation - i.e. lookup(), previous value(), running sum - all the calculations can be set and reset based on the dimensions in the viz
Now if you really need to avoid table calculations then the approach is to use conditional LOD expressions that incorporate an If statement to check against you fiscal year - not a real practical solution
If you still want to use the formula above you need to lock down the fiscal year in your calculation using an lod set to Max(fiscal year) If not tableau will look at the fiscal year on each record individually -
because table calculations are almost last in the order of operation you can use LOD expressions in the table calc but not the other way around
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
For the table calculation to restart at the fiscal year swich the default properties of your date:
Then set the default scope ("compute using" for each month) and you can recycle the table calculation in other views, just ensure it is still computing by the scope you originally set. You may have to add those pills (year and month) onto rows/columns or the marks card to affect the viz level of detail. Let me know if this helps or please attach a sample workbook representative of the structure of your data.
This is exactly what I needed. I was unaware of the Fiscal Date Start feature. Thank you for sharing,