Thanks for your answer. Unfortunately, it won't works for me as if I want to reduce my dimension at Year level or expand it at daily level, the lookup -12 will be false.
Interesting problem, and I must admit I learned something new thanks to you.
These two threads should definitely have answers to what you're looking for,
I've used Gerardo's approach and added couple more layers to give week and day,
And so on,
Hope this helps.
Dynamic Offset Lookup.twbx 2.4 MB
1 of 1 people found this helpful
Glad you found my other post useful! Since we aren't doing different calculations at different hierarchy levels all you have to do is set LOOKUP(SUM(Sales),-1) at the year level.
Workbook vs 2018.2 attached.
Awesome! This is neat. So when I added date level and set the offset to -366. The prior year for day starts somewhere in March and not January, this is because there are few dates missing in the data set. I was banging my head on how to solve this problem and you did this with one line of code.
Thanks for your answers, this is good to know.
However do you know if it exist a solution without using lookup function ? I want to create an expression that we can use in all type of object and the lookup is difficult to manage for every cases. Moreover, here we use a comparison with last year same date , and I wanted also to create another expression which will be a comparaison with last year but at the same day of the week (so not doing dateadd('year',-1,order_date) but by doing dateadd('day',-364,order_date) )
Any ideas ?
Thanks again for the help.