cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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

- 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

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors