Window calcs (like lookup) only work with fields that are in your viz - on the row or column shelf or on most marks cards (not tooltips). You only have your date on the filter pane so it cant be used by the lookup calculation.
Can you explain a litlle more about what you'd like to accomplish? For the 4 month period in your viz do you want to see the average MoM change for each pivot field value? Or do you want individual MoM values for each month (after the first month) in that date period for each pivot field value?
Thank you. I'd like to show the trend whether the numbers are going up or down for each category in the selected time frame. It could be shown as the end date value less average for the period or end date value less start date value (whichever is easier).
Thanks for clearing that up Agata. Unfortunately the way that you have this structured makes it hard to see which values fall between that date range. You can aggregate the values (sum, avg, max, etc) but finding the last value and subtracting the first is difficult without actually putting the date onto your viz.
The only solution I can think of would involve adding month to your viz, using a table calculation to figure out the change from the first value to the last, and then hiding the month values. The only downside with this is that your date filter wouldn't work anymore, so I don't think that's a viable option for you.
I'm hoping someone else here on the forums may have another idea.