Calculating Variance dymanically

Question

Hi All,

I have requirement to calculate Day over Day Variance. And user can select the Date  from the prompt, and i need to calcualte previous day of it to use in my formula.

Right now i am using the below formula, it is causing performance issues as i have nearly 20 columns with the same calculations.

DoD sales = Filter(sum(sales) using Date={today}) - Filter(sum(sales) using Date={previous day}).

Is there any other easier way of doing this so that i dont see performance issues.

3193829 · Answer

yes Manoj, if i get -1 right i can use the same for -7.

Joel Acha · Answer

This scenario tends to crop up quite frequently in this forum. If you want to perform trend analysis, you can only do this properly if you have a well defined date dimension in your underlying data warehouse which is in turn modelled correctly in your repository with a chronological dimension and hierarchy configured.

You could in theory do what you want without the date dimension but you will run into performance issues (as you've already discovered).

Manoj Dixit · Answer

Your original post did not mention about -7 days?