I would like create a calulated measure that is the sum of the current and prior three time periods. Currently, I create 3 separate period over period measures then create a formula that adds the 4 measures together into a single measure. This puts a heavy processing load on the visualization and slows down the rendering. My question, is there a better way of doing this?
calculate rolling sum of time periods
There are other faster solutions, however, they would be more difficult to implement.
For instance, you may create a Custom Formula plugin, and calculate all 4 points in one go (three offsets and the total of current and prior three time periods) and use that custom function in a Formula measure.
The period over period will typically issue another query back to your data source. Instead, you can use the momentum formula that will allow you to get the difference between periods (i.e. this period and previous period). You will still need to create this formula 3 times and then another formulas that multiplies the current period times 3 and add the other 3 momentum formulas measure but this will only issue 1 query back to your database as the formula is executed on the existing data and hence should be less taxing.
Ariel, the momentum function helped and delivered better performance. Thanks
Fantastic - glad it helped :)