Previous Row Value from the same column

[murillocosta]

Hi. Would someone able to help me with the below?

I need to calculate a new column named "Total Outbound FINAL". This columns will need to sum the last 7 "Total Outbound" column rows values

- For example if I want to calculate 2023-W10 like below I will need to perform the calculation

86 + 317 + 353 + 301 + 339 + 254 + 308 = 1958

Calculating the first rows is fine as you can see in the screen above.

My question is how to calculate the next row and also include 2023-W10 as one of the 7 previous row??? So my 2023-W11 should be:

1958 + 86 + 317 + 353 + 301 + 339 + 254 = 3608

The expected result should be like this

Thanks

[MAwwad]

To calculate the rolling sum of the last 7 rows of the "Total Outbound" column, you can use the DAX function called SUMX. Here is the formula you can use:

Total Outbound FINAL = SUMX( FILTER( 'your_table_name', 'your_table_name'[YearWeek] <= MAX('your_table_name'[YearWeek]) && 'your_table_name'[YearWeek] > MAXX('your_table_name', 'your_table_name'[YearWeek]) - 7 ), 'your_table_name'[Total Outbound] )

In this formula, replace 'your_table_name' with the actual name of your table, and 'YearWeek' and 'Total Outbound' with the names of your columns.

This formula uses the MAX function to get the maximum value of the "YearWeek" column, and then uses MAXX to get the maximum value of the "YearWeek" column in the previous row. It then filters the table to include only the rows with a "YearWeek" value less than or equal to the maximum "YearWeek" value, and greater than the maximum "YearWeek" value minus 7 (to get the last 7 rows). Finally, it sums the "Total Outbound" column for these filtered rows.

Note that this formula assumes that your "YearWeek" column is formatted as text in the format "YYYY-W##". If it's formatted differently, you may need to adjust the formula accordingly.