Cumulative sum in powerquery editor

streli To calculate the cumulative amount while excluding the unit values with earlier expiry dates in Power Query Editor, you can use the following steps:

Assuming your table is named "YourTable" and the columns are named "DateDay," "ExpiryDate," and "Volume," follow these steps in Power Query Editor:

Step 1: Sort the data:
Sort the table by the "DateDay" column in ascending order and then by the "ExpiryDate" column in descending order. This will ensure that the unit values with earlier expiry dates are listed first and will be excluded from the cumulative calculation.

Step 2: Add an Index Column:
Add an Index column to the table. This will create a unique identifier for each row, which is essential for the next steps.

Step 3: Duplicate the table:
Duplicate the table using the "Duplicate" feature in Power Query Editor. This will create a copy of the original table that we can use for the cumulative calculation.

Step 4: Merge Queries:
Merge the original table with the duplicated table based on the "DateDay" and "ExpiryDate" columns. The merge should be a Left Anti join, which will keep only the rows from the original table where there is no match in the duplicated table (i.e., where the unit values have an earlier expiry date). This will effectively exclude those rows from the cumulative calculation.

Step 5: Group and Aggregate:
Group the resulting table by "DateDay" and aggregate the "Volume" column using the "Sum" function. This will calculate the cumulative amount, excluding the unit values with earlier expiry dates.

Step 6: Merge the Results:
Merge the aggregated table back with the original table based on the "DateDay" column. This will bring the cumulative amount back to the original table.

Step 7: Remove Unnecessary Columns:
Remove the duplicated table and any intermediate columns created during the process, keeping only the "DateDay" and "Volume" columns.

Now, your table should have a new column showing the cumulative amount while excluding the unit values with earlier expiry dates.