How to Create a Moving SUM with Visual Calculations and RANGE

Learn how to create a moving sum in Power BI using visual calculations! In this tutorial, Mitchell Pearson demonstrates how to leverage the powerful capabilities of visual calculations, including the use of the RANGE function and SUMX, to create a 3-month moving sum directly in Power BI visuals. Follow along to see how these techniques allow you to calculate dynamic rolling totals that update as you move through time-based data.

What is a Moving Sum?

A moving sum provides the cumulative total of values over a defined window of time. For example, a 3-month moving sum for April would include data from February, March, and April. As you progress through the timeline, the sum "moves" by dropping the oldest month and including the current one.

Why Visual Calculations?

Power BI’s visual calculations feature allows you to apply transformations directly within visuals, simplifying complex calculations without needing extensive DAX coding. While moving averages are a built-in option, creating a moving sum requires a different approach using DAX functions.

Step-by-Step Guide

1. Start with Visual Calculations

• Begin by creating a running sum to see cumulative totals over time.
• Adjust optional parameters like blanks handling and reset logic for quarter-to-date or year-to-date totals.

2. Use the RANGE Function

• The RANGE function selects a specific set of rows relative to the current row.
• For a 3-month moving sum, specify the range as RANGE(-2, 0), which includes the current month and the two prior months.

3. Calculate the Moving Sum

• Wrap the RANGE function in SUMX to iterate over the selected rows and sum the desired column values.
• Combine these functions to create a dynamic calculation that adjusts as the data changes.

Benefits of this Approach

• Directly integrates into Power BI visuals for dynamic and responsive calculations.
• No need for complex data model changes or pre-aggregations.
• Works seamlessly with time-based data to provide clear insights.

Final Thoughts

This method of creating a moving sum debunks the myth that it cannot be done within Power BI’s visual calculations. By utilizing the RANGE and SUMX functions, you can create sophisticated time-based calculations with ease. 

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on DAX and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.