Relative time function with missing data

Question & Answer
1

Hi all,

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:

image
image554×525 26.7 KB

What is the best way of displaying a prior year number when no record exists in the current year?

Thanks,

Ben.

2

Hi,

Thanks for reaching out. I think you are going to need to solve this at the dataset level by ensuring that period is in a separate table and then using a left join on period. This will force entries into the pivottable for all periods and would force an entry for 201910 in your pivot above which should fix your calculation.

4

Hi, @bdawson. Did Sean’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks!