10 day prior variance & %variance calculation

Question

Hello,

I would need to calculate 10 day prior variance column wise.

For Instance, if the column date is 5th October, I would need to calculate the variance for 10 days prior to next date in 'Date of Release' Column. ie Value for date 6th October - Value for 26th September and also % Variance. For dates in Column that don't have next date in 'Date of Release', we would need to take the last record to calculate the variance value.

Can someone please help me on how to calculate variance & &Variance?

Thanks.

Data.xlsx

echuong1 · Answer

You can pivot the data to get all values into a vertical column. From there, you can use the datetimeadd function and a filter to identify records within the date range. You can then use the sample tool to grab the beginning and ending value. A formula tool will calculate the variance and variance %. You can pivot the data back with a cross-tab and join if needed with the original data.

New Workflow1.yxzp