Rolling Sum using two columns in Power BI

Question

I've two columns containing sales from last year and this year and a data column with the number of weeks we are in. I want to calculate a yearly rolling sum in a new column from the week we are in back in the past till that same week.

In my example if i'm in week 2 this year my rolling sum will be sum all the values of sales last year from week 2 til week 52 plus sales from this year until week 2 including!!

Here's an example in excel of what my table and results would look like:

enter image description here

Take a look at this stackoverflow.com/questions/46891153/running-total-of-a-measure and this sqlbi.com/articles/computing-running-totals-in-dax — Angelo Canepa
@AngeloCanepa the problem is that i don't have the sales in the same column they are both in two sperate columns that's why i'm struggling ... — hurop

Angelo Canepa Angelo Canepa · Accepted Answer · 2020-10-30T09:36:15

Assuming your data look like this

Table
+------+----------+-------+
| week | sales_ly | sales |
+------+----------+-------+
| 1    | 65       | 100   |
+------+----------+-------+
| 2    | 93       | 130   |
+------+----------+-------+
| 3    | 83       | 134   |
+------+----------+-------+
| 4    | 3083     | 59    |
+------+----------+-------+
| 5    | 30984    | 39    |
+------+----------+-------+
| 6    | 38       | 580   |
+------+----------+-------+
| 7    | 28       | 94    |
+------+----------+-------+
| 8    | 48       | 93    |
+------+----------+-------+
| 9    | 24       | 984   |
+------+----------+-------+
| 10   | 49       | 95    |
+------+----------+-------+

You need to create two cumulatives and sum them in the same measure.

Rolling Sum = 

VAR CurrentYearCumulative = 
    CALCULATE(
        SUM('Table'[sales]),
        FILTER(ALLSELECTED('Table'),'Table'[week] <= MAX('Table'[week] ) )
        )

VAR LastYearCumulative = 
    CALCULATE(
        SUM('Table'[sales_ly]),
        FILTER(ALLSELECTED('Table'),'Table'[week] >= MAX('Table'[week]) )
        )

RETURN

CurrentYearCumulative + LastYearCumulative

The output

Rolling Sum using two columns in Power BI

1 Answers