Calculate with data in previous rows fails when year changes

Hi Wesley0107 ,

If you want your balance to continue across years (so it doesn’t reset every January), you just need to tweak the way you filter previous rows in your calculation.

 

Here’s what to do in Power Query (M): Instead of filtering by both [Index] and [Year], just use [Index] (and any grouping key you need, like EmployeeID). Here’s a template:

let
    AddCumulative = Table.AddColumn(PreviousStep, "Balance", (currentRow) =>
        let
            PreviousRows = Table.SelectRows(PreviousStep,
                (r) =>
                    r[Index] <= currentRow[Index]
                    // Include grouping key here if needed, e.g.:
                    // and r[EmployeeID] = currentRow[EmployeeID]
            ),
            RunningTotal = List.Sum(PreviousRows[Change])
        in
            RunningTotal
    )
in
    AddCumulative

 

That way, your running total simply keeps going, regardless of the year.

If you’re using Power BI and prefer DAX, here’s a similar approach:

Balance = 
CALCULATE(
    SUM('Table'[Change]),
    FILTER(
        ALL('Table'),
        'Table'[EmployeeID] = EARLIER('Table'[EmployeeID]) &&
        'Table'[Index] <= EARLIER('Table'[Index])
    )
)

 

Just replace EmployeeID with whatever field(s) you want to group by.

Make sure your [Index] column is created after sorting your table exactly the way you want the calculation to run—otherwise, the running total can go off track.

Hi Wesley0107 ,

 

Here I create a sample to have a test and it should work on myside. You can download my attachment and compare my sample with yours.

let
 Limit = 0,
// Obtain Current Index and ID
CurrentIndex = [Index],
CurrentEmployeeID = [ID],
// Obtain the change
CurrentChange = [Change],
// Filter the rows for the same ID but with a lower index
PreviousRows = Table.SelectRows(#"Added Index", each [ID] = CurrentEmployeeID and [Index] < CurrentIndex),
// Calculate the cumulative sum
CumulativeSum = List.Accumulate(
    PreviousRows[Change], 
    0, 
    (state, current) => 
        let
            NewSum = state + current
        in
            if NewSum < Limit then Limit else NewSum
        ),
// Calculate the total balance including the current change balance
ChangeDeviation = [Change],
AdjustedTotal = CumulativeSum + ChangeDeviation,
// Check limit
FinalBalance = 
if AdjustedTotal < Limit then Limit else AdjustedTotal
in
FinalBalance

Result is as below.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.