I'm not sure what happened to my last answer to this one.
When you quick filter, you eliminate rows from the table underlying your sheet. If you pick January, then December is gone, and the LOOKUP to see the prior month has nothing to grab.
Use a table calc as your filter in these cases, and your underlying table remains intact. A table calc only governs what is displayed, not what is brought in from the data source.
I made a LOOKUP calc to use as a filter.
test 1.twbx 21.7 KB
Thank you, Joe,
Your solution works, and as you mentioned above Sort is a problem, I am trying the parameter approach and struggling to figure out that part. if possible can you help me with that
If you are always going to allow just one selection at a time, then a parameter approach would work. (Currently, parameters are only single-select.)
For this example I just focused on MONTH. If month AND year are necessary, in the [Select a month (copy)] calc I built, I have a commented section for one way you would also take year into consideration.
So inside my LOOKUP I embedded 'IF' logic. Then set the values either to 1 or 0. When I put that on the filter shelf, I select only for value = 1.
Your 'IF' logic can be as complicated as you need in a thing like that. What matters is that you make the right conditions result in a value of 1.
test 2.twbx 22.5 KB
Building off of what Joe said, and more specifically addressing your question on how to calculate January compared to December of the previous year, here's a simple calc that should help!
and datepart('month',[date])>[Month Parameter]+10
and [Month] = 1
and DATEPART('year',[date])<= [Year]
and DATEPART('year',[date])<= [Year]-1
and DATEPART('year',[date]) = [Year]
It's been almost a year so I hope you've already solved this, but I wanted to post it here in case anyone else was having issues as well.