We’re attempting to include a prior year measure for sales. However, we can’t use the inbuilt date functions due to our custom calendar.
We’ve tried various iterations of this, but the closest has been using the WindowSum function, which (at face value) provides the result we’re looking for. The formula used to achieve this was:
windowSum(sum(sales),[period ASC],11,0) - windowSum(sum(sales),[period ASC],10,0)
However, this falls apart when we bring “region” into the mix, as some periods do not have a sales record. This means that the prior 11 records span more than a year:
What is the best way of displaying a prior year number when no record exists in the current year?