There is a unique technique in Power BI with which you can calculate the Cumulative Totals on monthly average results.

Firstly, you will need to set a date range for calculating the Cumulative average results. For this, you will require a date slicer. This helps narrow down a portion of the data set shown in the visualizations of the report. It is used to limit close date ranges in Power BI.

However, the cumulative total is used to display the data’s summation as it will grow with time or any similar progression. Usually, while reviewing cumulative totals, they are analyzed over a particular date, month, or year. This article further shows how you can use Power BI to calculate the cumulative totals over a month.

How To Calculate Cumulative Totals On Monthly Average Results for a Business?

First of all, you will need to understand the period to period change. To do that, begin by creating an expression in DAX. DAX is a library of operators and functions that help build expressions and formulas on Power BI. The expression you created will help calculate the total sum of sales.

With that done, you will need to create a measure known as ‘Difference.’ This will further represent the difference in sales for each month in the year. This can easily be calculated by taking the sum of the current year’s sales and subtracting it by the sum of the previous year’s sales.

However, to calculate the sum of the previous year’s sales, you have to use three functions. Namely, they are CALCULATE, DATEADD, and SUM. This will help you in three ways. It will tell you the sum of sales, specify the dates you need to use, and show the interval. While -1 denotes the previous year, -2 indicates the last two years, and so on.

After you have the Difference and Sum measures, the last step is to calculate the cumulative sum. You have to start by denoting your _mnth variable. The keyword VAR declares the definition of a variable. You can have as many variables as required in one expression, and each will have its own VAR.

The keyword RETURN denotes the expression to return. Inside this expression, you can use variables, which are then replaced with the computed value. Using the VALUES function and SUMx function, you can know if a table is going to be returned.

You specifically have to sum the Difference measure for each month. After that, the ALL function clears the filters from each month. All that is left to do now is check if the months you are summing come before the current date.

In Conclusion

With this particular method, you can get the ultimate cumulative average based on dynamic date logic. However, only the basics of the formula have been covered in this blog. You can find more of such tutorials on Enterprise DNA. They also provide elaborate training sessions on other functions and uses of Power BI.

The post Preliminary Steps to Calculate Cumulative Totals on Monthly Average Results appeared first on Magnetoz.