# Total revenue chart increased by time

Hi all, I have revenue chart from Potential orders in my app as below, with value of each day is a sum of total product revenue of each day,

How could I make a chart that show value increase by time, the current column is the total of all previous column, for ex: if 1st Jan sum total rev is \$20, 2nd Jan \$15, 3rd Jan \$15, then the chart will show 1 Jan \$20, 2nd Jan will be \$35, and 3rd Jan will be \$50 ?

Below is my potential order for your reference, thanks

Hello @Phong_Lam, there a couple of ways that you can solve this, basically what you need is a column that saves the accumulated revenue for each row, and you can do this in two ways:

1. Create a virtual column with a simple expression that sums all [Total] values for the current year up to the date in which the row itself was added

2. Add a new â€śnormalâ€ť column, hide it, and use it to calculate using the same idea as 1)

The option 2) is better for performance, since the calculation is done once and thatâ€™s it, but it depends on how your app works, if there can be modifications to earlier transactions in the year in such a way that it affects the [Total] column, then the accumulated revenues in the next months will be wrong, but if such thing canâ€™t happen, then itâ€™s the best approach I believe.

3 Likes

Hi @Rafael_ANEIC-PY , do you know what is that simple expression ? Sorry I am bad at the expression â€¦

Sure, Iâ€™ll give it a shot and let me know if it works for you:

``````SUM(
SELECT(
Potential order[Total],
AND(
YEAR([_THISROW].[Date])=YEAR([Date]),
[_THISROW].[Date]>=[Date]
)
)
)
``````

If there are other variables to consider then we can add those considerations inside our AND() expression.

If you wanted, you could create a slice that lists only the current year orders, that way we could optimize that expression a little bit further hehe

1 Like

Hi @Rafael_ANEIC-PY , Itâ€™s not work, Itâ€™s sum all products from all customers at that that day, and duplicated, the revenue of 13 is greater than 14thâ€¦

I see, the results should be:

• 1/11 = 142
• 1/12 = 242
• 1/13 = 295
• 1/14 = 495

I canâ€™t see the date 1/12 in there, I suppose you went with the virtual column approach here, added my expression there, and then used the virtual column as the data column for the graph?

1 Like

Yes, I used your solution 1 virtual column,the chart above is a ref view from customer f5797fe8, so It dont have the 1/12

I see, the problem here is that the virtual column that I suggested correctly returns the accumulated revenue for each given day, but the graph itself SUMS the [Total accumulation] column.

So instead of getting the accumulation for the day, youâ€™re getting the sum of all the accumulations for each day, which is of course, wrong.

Also, the expression doesnâ€™t differentiate between different costumers, that requires aditional logic, and was not requested in your original post, so i didnâ€™t take that into account.

2 Likes

Hi @Rafael_ANEIC-PY , do you know how to do it ?