Forum Discussion

murillocosta's avatar
murillocosta
Helper I
3 years ago

Previous Row Value from the same column

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   -...
MAwwad's avatar
MAwwad
Solution Sage
3 years ago

 

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.