Efficient Week over Week calculcations

[marinof]

Hi,

I have a lot of week over week measures in my model. At the moment the way those are implemented is as follows:

MeasureWoW := ([Measure]-[MeasurePrevWeek])/[MeasurePrevWeek]

MeasurePrevWeek := Calculate([Measure],Dateadd(calendar[Date],-7,Day))

Since these calculations are the heaviest in the model, I'm wondering whether there is a way to achieve the same in a more efficient way. Since I have many tables related to the calendar table, I'm afraid that changing the filter context of the calendar table as I do it is a very expensive operation as it propagates to all these tables even if I don't need them in the calculation of the measure at hand. So I'm thinking of creating a separate calendar table only related to the tables needed for the calculation of the measure and perform the same operation on that table. Does anyone know if that could help or is there a better way altogether?

Marino

 

[miguel.escobar]

Hey Marino!

It really depends and it's actually a case by case scenario but, what about using your calendar columns? for example, do you happen to have numeric columns that show the week# or the specific period time?

What you've typed before it's probably the easiest way to do so and one of the most optimal ways to do it so maybe the biggest impact's being caused by your slicers/filters?

 

[Jacob Barnett]

I would say the dateadd function is the best way to deliver a dynamic 'period over period' calculation and I've tried a lot of different options!