Formula to add up the revenue for each month (which is each row)

Hi,

I have a sales tracker which includes the following:

  • Each row is the month, i.e. Jan, Feb, Mar
  • I have a column for revenue, which counts the revenue in that month
  • I have a column for cumulative revenue which ideally should work as follows: Jan = Jan revenue, In Feb = Jan + Feb revenue, in Mar = Jan + Feb + March revenue

How do I do this?

Hi @Jason_FuzzyLogic

Interesting one. To be able to do such a thing the system needs to know the order of thing (in this case your months). As there is no specific column type for months it would be difficult to understand the order and thereby create a cumulative formula. It looks to me this can be done with a monday app (integration) but the point that somebody should let the system know the order of things will be the same.

Very interested how other look at this.

I was able to get this to work if the items are linked. Mar linked to Feb, Feb linked to Jan, etc. Jan linked to nothing. Had some trouble getting the right formula. But finally got it working.

Here’s what I did:

  1. Create a formula column “cumulative revenue”. Set the formula to anything.
  2. Create a link column (don’t allow multiples) to the same board. Mirror the “cumulative revenue” to “Prev CR”.
  3. Edit the “cumulative revenue” formula to: SUM(CONCATENATE({Prev CR}),{revenue})
  4. Set the links to each row.

It’s probably possible to use an automation to create the linked records if needed.

This is really clever and works, nice work.
I’ve found an interesting issue which I ‘think’ is a bug but need to check:

I have three months populated and for demo sakes and added
Feb - 100,000
Mar - 200,000
Apr - 300,000

So the cumulative revenue is 600,000 and it’s correctly showing that in the field.
I’ve then added a Chart and added the cumulative revenue field in the X-axis. When this is displayed on the chart it shows 100,000 200,000 and 6,000,000 - for some reason the third value gets a zero added.

Interestingly if I add another months data then the issue displays on the new month, adding the zero there (and not on the previous months).

Great aproach Jim! I know I’m close to getting a solution with your guide but there’s something I’m missing, by any chance may you be so kind to share a video with the process or share an example board please?

Hey @iker.rivera! Could you give me a little more detail about what you’re missing? Where in the process are you getting stuck? I’d love to know more information so I can help :blush: Any screenshots of where you’re at are great!

Hey Jim! I’d love to use this workaround, but I’m not sure how to “link” columns barring using the mirror function. Do you have more detailed instructions or a video I could follow? (My use case is to track revenue month-by-month, and then cumulatively.) Thanks so much for any help/direction you can share.

@AEstrich ,
Using the standard linking & mirror columns is the way to go. It’s just a little odd because you’re linking the board to itself.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.