How to calculate the cumulative net sum/count

Workbook Operations
1

Say you’re leasing apartments, and you have move in / move out data per resident for the current year, e.g.

image
image1054×928 69 KB

If you wanted to calculate the cumulative net sum of residents or cumulative net rent income over time in this table, one way you could do this is…

  1. Make a table summary called [Starting Count of Residents] or [Starting Rent Income Total] and assign the numeric value to it in the formula bar (or write a formula that provides a numeric output for this value). If you don’t have a starting resident count (e.g., the final resident count from the prior year), or a starting rent income total (e.g., the final total of rent income from the prior year), feel free to skip this step.
  2. Set-up your table’s groupings.
    • Group by [Date] first (I chose the day of date truncation level), then sort ascending.
  3. Make a helper column.
    • In the [Day of Date] grouping level, add a new column called [Net Residents] with the formula: CountIf([Move In \/ Move Out] = "Move In") - CountIf([Move In \/ Move Out] = "Move Out").
      • Note: the above formula simply calculates the move in row count minus the move out row count. If you wanted to calculate the move in rent income minus the move out rent income as [Net Rent Income], you’d use this formula instead: SumIf([Rent], [Move In \/ Move Out] = "Move In") - SumIf([Rent], [MoveIn \/ MoveOut] = "Move Out").
  4. Make your final calculation column.
    • In the [Day of Date] grouping level, create a new column called [Cumulative Net Count of Residents] with the formula: CumulativeSum(If([Day of Date] = First([Day of Date]), [Starting Count of Residents], 0) + [Net Residents]).
      • Note: if you didn’t do step (1) because you didn’t have a starting value, the formula would be: CumulativeSum([Net Residents]). Also note: if you wanted to calculate the cumulative net rent income instead, you’d use the formula: CumulativeSum(If([Day of Date] = First([Day of Date]), [Starting Rent Income], 0) + [Net Rent Income]) (or, if you didn’t have a starting rent income value to begin with, you’d use CumulativeSum([Net Rent Income])).
2 Likes