First, I would create a parameter that you will use to filter your date range (in my example, I'm using the month name):
Then, I create a logical calculated field that basically filters out the other months while keeping the value needed for the MoM calculation:
Add this to the filter shelf and select true. Now, the viz should show only the month you want to see, along with the MoM calculation:
I hope this helps!
The problem is that LOOKUP is a table calculation function. Table calculations do further calculations on the data displayed in your viz - if you filter out the previous year prior to the table calculation being computed (regular dimension / measure filters are computed before table calculations), then LOOKUP will not find the previous year's data.
You may be able to work something out by filtering using a table calculation instead of your normal year filter though. Try creating the calculated field [Index] : INDEX(), then filtering on [Index] = 1. If you set index to compute using your year variable, Tableau may calculate the LOOKUP prior to the data being filtered.
Could provide more detail on your solution? I'm not advanced with Tableau calcs and I don't know what to INDEX. I created the parameter, but can't get a valid calc.
Sure, I can elaborate. First set up your viz the way you want it without the "most recent month" filter. Then create the following calculated field
[Index] : INDEX()
Pull this calculated field into the "Filters" area and keep only the value 1. Then right click on the [Index] pill in the Filters card, and under Compute Using, select [Order Date]. This will keep only the first year / month / day / other unit of order date displayed in your table. You may need to change the sort on [Order Date] in order to get the most recent month to show as opposed to the first month.
Basically what the INDEX function does is enumerate the displayed values according to whatever is set in the "Compute Using" setting. I would encourage you to read through the following article for more information on how Table Calculations work in Tableau: