Total revenue chart increased by time

[Phong_Lam1]

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

[Rafael_ANEIC-PY]

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.

[Phong_Lam1]

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

[Rafael_ANEIC-PY]

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

[Phong_Lam1]

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…

[Rafael_ANEIC-PY]

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?

[Phong_Lam1]

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

[Rafael_ANEIC-PY]

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.

[Phong_Lam1]

Rafael_ANEIC-PY:

Also, the expression doesn’t differentiate between different costumers, that requires aditional logic,

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