Hi, I have a field that I use to calculate my Closing SOH. Basically it's all the movement in and out by day. It dates back to 2009. So whenever I want to see my closing SOH for a particular date, I need to sum all the transactions from 2009 right up to the date I've selected. The syntax I use for that is:
sum({$<[Tran Date]={'<=$(vMaxDate])'}>}Quantity)
Now, I want to see what was the Closing SOH at the end of week 42 in 2015, 2014 and 2013.
When I add the Year and Week as dimensions, the formula breaks.
I'm able to get it right if I Pivot it and create 3 different expressions:
sum({$<[Fin Year]=, [Fin Week]=, [Tran Date]={'<=$(vMaxDate)'}>}Quantity)
sum({$<[Fin Year]=, [Fin Week]=, [Tran Date]={'<=$(vMaxDateLY)'}>}Quantity)
sum({$<[Fin Year]=, [Fin Week]=, [Tran Date]={'<=$(vMaxDateLLY)'}>}Quantity)
But I need it as one expression in a straight table.
Can anyone help me?