Insights Cumulative Sum by Period to Period Change in Power BI

Cumulative Sum by Period to Period Change in Power BI

To first understand period to period change, you want to start by creating an expression in DAX (a library of functions and operators that can build formulas and expressions in Power BI Desktop) that calculates the sum of sales. You can do this by writing a measure like the following:

Sum = SUM(‘Internet Sales'[Sales Amount])

Next you want to create a measure called “Difference” representing the change in sales each month for one year. To calculate this, we take the sum of sales for the current year and subtract the sum of sales from the previous year. To calculate the sum of sales from the previous year, we want to use three functions: CALCULATE, SUM and DATEADD. This sums the sales, specifies which dates to use, and the interval (-1 represents the previous year, likewise, -2 represents the previous two years).

Difference = [Sum]- CALCULATE(SUM(‘Internet Sales'[Sales Amount]), DATEADD(‘Date'[Date], -1, YEAR))

Now that we have the Sum and Difference measures, we just need to calculate the cumulative sum. This is where it can be a little tricky. We start by declaring our _mnth variable. The VAR keyword introduces the definition of a variable. You can have as many variables as needed in a single expression, and each one has its own VAR definition. The RETURN keyword defines the expression to return. Inside the RETURN expression, you can use the variables, which are replaced by the computed value. We use the SUMX function and the VALUES function to signify that a table is going to be returned. We specifically want to sum our Difference measure each month. Next, the ALL function clears filters from our months. Lastly, we check to see if the months that we are summing come prior to the current date. The following code further creates the graph below.

Cumulative Sum =

VAR _mnth = SELECTEDVALUE(‘Date'[Month])

RETURN

 CALCULATE (

    SUMX (VALUES(‘Date'[Month]), [Difference])

    ,ALL(‘Date'[Month])

  , ‘date'[Month] <= _mnth

    )