Amazing @Jesse , super clear really. Thank you.
I see only one drawback here, if you don’t have an ordered list of period (eg. W1,W2 and then W6) or, more probably, an increasing number of loaded weeks (eg. first update W1,W2… second update W1,W2,W3…etc…) the number on lag function or filtering the rank need to be dynamic for YoY.
Eg.
FIRST SITUATION
2021-W2 —> to get YoY here, I should use instead of “52” in the lag function, the number “3”. As well as for filtering the rank field.
2021-W1 —> hide
2020-W2 —> hide
NEXT UPDATE
2021-W3 —> to get YoY here, I should use instead of “52” in the lag function, the number “4”. As well as for filtering the rank field. (new)
2021-W2 —> hide
2021-W1 —> hide
2020-W3 —> hide (new)
2020-W2 —> hide
Is there a way to automated this by, for eg., a dynamic parameter or another calculated fields?
Changing manually the number on lag function or on rank filtering, would be the same as using a manually inputted week into the formula.
I’m opened to any kind of solution, also changing the dataset design.
Thanks,