Data Studio - Dynamic Filter Control
I'm looking to produce a 'Score Card' filtered & summed for certain 'Week Numbers', effectively it's the sum of the last employee payrun. These 'week numbers' I'm looking to filter, don't follow a perfect two week cycle. I have an equation that determines what the week numbers are, relational to 'today's date'. (see below).
Is there a way of filtering the results based on an 'individual cell value', in this case A2, that changes rather than filtering on a set date time frame, such as 2 weeks etc, or a fixed name or number?
My current work around is a vlookup array column in 'Google Sheets' (the source) that effectively assigns a Yes/No value to the rows, and then creating a Case filter based on 'Yes' for example.
Formula to produce the last pay week, A2. A1 produces the current week Number.
A2 =if(iseven(A1-2), A1-2, A1-3)
Any thoughts greatly appreciated.