Sum for each row including all previous rows

[Jonathan_S]

Hey everyone,

Let say you have a number column and a date column.

The rows are in order starting from oldest to newest.
There may be rows that have the same date, but they will be in order.

Im wondering if its possible to iterate throughout each row, and sum total all previous dates with the currant row date. This would be done for each for, row. If there is more than 1 date of the row it is working on, only use the latest entry (Last row with that date).

I want to display this is a chart, but also dont want duplicate values for the dates that contain more than 1 entry.

Any input would be appreciated.

[Bellave_Jayaram]

It’s possible. Could you share a sample of the rows and expected chart (hand drawing is ok) you want to see?

[Jonathan_S]

I would Need to do a few more calculations with the resulting numbers but in the end I want to create a chart like so, this is just a sample pic.

[Jonathan_S]

Ive tried things like

SUM(SELECT([Number],[Date]<=[_THISROW].[Date]))

[Steve]

Jonathan_S:

Im wondering if its possible to iterate throughout each row, and sum total all previous dates with the currant row date.

Yes.

Jonathan_S:

If there is more than 1 date of the row it is working on, only use the latest entry (Last row with that date).

This makes it a lot more difficult.

Is it important that each row have the sum? Or is it enough (or even preferred) that only the last entry for a given date have the sum?

[Jonathan_S]

The last entry for a given date.

I will than than need to subtract the previous Day and than divide that by the previous day.

[Rome18]

Hi @Steve , what if each row need to have the sum? I am trying to make historical transaction Apps that shows the balance of each row even though it have 2 or more data in the same date.

[Jonathan_S]

Any Feedback?

[Steve]

You want to compare today’s revenue (not this month’s) to last month’s revenue?

[Jonathan_S]

Yes. I dont have enough data for months yet so I want to test everything out with days first, than move to months.

[Steve · Accepted solution]

I envision using actions to get what you want. See below.

Let me know what your table and column names are and I’ll update this recipe. Specifically, for the MyTable table and Growth Rate (Revenue), Revenue (last month), Revenue (today), and Date columns.

After defining the actions, attach the MyTable::Update Growth Rate (Revenue) action as the Form Saved event action on the form that populates Revenue (today).

NONE OF THIS IS TESTED!!!

DATA LOSS MAY OCCUR!!!

USE AT YOUR OWN RISK!!!

Action MyTable::Clear Growth Rate (Revenue)

• For a record of this table: MyTable
• Do this: Data: set the values of some columns in this row
• Set these columns:
  • Revenue (last month): ""
  • Growth Rate (Revenue): ""
• Only if this condition is true: ISNOTBLANK([Growth Rate (Revenue)])

Action MyTable::Clear earlier Growth Rate (Revenue)

• For a record of this table: MyTable
• Do this: Data: execute an action on a set of rows
• Referenced Table: MyTable
• Referenced Rows:

  FILTER(
    "MyTable",
    AND(
      ([_THISROW].[Date] = [Date]),
      ([_THISROW].[_ROWNUMBER] > [_ROWNUMBER]),
      ISNOTBLANK([Growth Rate (Revenue)])
    )
  )
  

• Referenced Action: MyTable::Clear Growth Rate (Revenue)
• Only if this condition is true:

  ([_THISROW] = MAXROW(
    "MyTable",
    "_ROWNUMBER",
    ([_THISROW].[Date] = [Date])
  ))
  

Action MyTable::Set Growth Rate (Revenue)

• For a record of this table: MyTable
• Do this: Data: set the values of some columns in this row
• Set these columns:
  • Revenue (last month):

    SUM(
      SELECT(
        MyTable[Revenue (today)],
        AND(
          ([_THISROW].[_ROWNUMBER] > [_ROWNUMBER]),
          ISNOTBLANK([Growth Rate (Revenue)])
        )
      )
    )
    

  • Growth Rate (Revenue):

    (
      ([Revenue (today)] - Revenue (last month))
      /
      Revenue (last month)
    )
    

• Only if this condition is true:

  ([_THISROW] = MAXROW(
    "MyTable",
    "_ROWNUMBER",
    ([_THISROW].[Date] = [Date])
  ))
  

Action MyTable::Update Growth Rate (Revenue)

• For a record of this table: Mytable
• Do this: Data: execute a sequence of actions
• Actions:
  • MyTable::Clear earlier Growth Rate (Revenue)
  • MyTable::Set Growth Rate (Revenue)
• Only if this condition is true: ISNOTBLANK([Revenue (today)])

[LeventK]

@Steve
You’re an expression beast!!

[Jonathan_S]

Man your going to need to give me some time to wrap my head around this.

@Steve So I need a virtual column for the Growth rate and Revenue? or a real column?

[Steve]

Long Summary

1. When the user saves the form that includes the Revenue (today) column, the MyTable::Update Growth Rate (Revenue) (2) action is triggered by merit of being the Form Saved event action for the form.

2. If the Revenue (today) column from the saved form is not blank, the MyTable::Clear earlier Growth Rate (Revenue) (3) and MyTable::Set Growth Rate (Revenue) (5) actions are performed.

3. If The current row (saved from the form, in (1)) has the highest row number of rows with the same date, the MyTable::Clear Growth Rate (Revenue) (4) action is performed on all preceding rows (those with lower row numbers) that have the same date and a non-blank Growth Rate (Revenue) column value.

4. If the current row (those identified by (3)) has a value in the Growth Rate (revenue) column, that value is cleared, as is the Revenue (last month) column value.

5. If the current row (saved from the form, in (1)) has the highest row number of rows with the same date, compute and set the Revenue (last month) and Growth Rate (revenue) column values.

Short Summary

When a row is added (1), all preceding growth rate calculations for the same date (3) are cleared (4) and the growth rate is recalculated with the new row (5).

Notes

Only the last row per date should have a non-blank Growth Rate (Revenue) column value. The “last row” is the row with the highest row number.

Revenue (last month) is calculated from Revenue (today) column values from preceding rows that have a Growth Rate (Revenue) column value. “Preceding rows” are those with a lower row number.

A non-blank Growth Rate (Revenue) column value is used to indicate that a row is the last row with its date, and is how the calculation of Revenue (last month) only uses one Revenue (today) value per day.

[Steve]

Jonathan_S:

So I need a virtual column for the Growth rate and Revenue? or a real column?

Real columns.

[Jonathan_S]

Your Big brain support has really come through, thanks so much