creating a graph with future revenue recognition

[Shir63]

hi all,

i was requested to prepare a graph where i show the future revenue recognition we had at each quarter over time.

i already calculated the value, and i have a column called revenue.

my problem is that the value is spread over , 2021 2022

here is an example:

Quarter Date start	Dist GL Date	Revenue	Order number
01-01-21 0:00	29/03/2021	151	123
01-04-21 0:00	29/04/2021	151	123
01-04-21 0:00	29/05/2021	151	123
01-04-21 0:00	29/06/2021	151	123
01-07-21 0:00	29/07/2021	151	123
01-07-21 0:00	29/08/2021	151	123
01-07-21 0:00	29/09/2021	151	123
01-10-21 0:00	29/10/2021	151	123
01-10-21 0:00	29/11/2021	150	123
01-10-21 0:00	29/12/2021	151	123
01-01-22 0:00	29/01/2022	150	123
01-01-22 0:00	28/02/2022	150	123

the total recognition is 1809.

i would like to present total of 1809 for the first quarter, and then for each quarter after that to reduce the amount we already recognized..

i used this formula, but seems like i need the exact opposite. cause i want Q1'2021 to have 1809, Q2' to have 1508 and so on....

RangeSum(above(Sum(Revenue), 0, RowNo()))

any ideas?

[diegozecchini · Accepted solution]

Hi!
the formula needs to calculate a cumulative reverse sum, starting with the total future revenue at the earliest quarter and reducing it as revenue is recognized quarter by quarter.

Ensure your dataset includes all the necessary columns, especially the Quarter Date Start and Revenue.
Instead of summing up cumulatively, you need to calculate the remaining revenue for each quarter by subtracting the cumulative recognized revenue up to that quarter from the total.

Sum(TOTAL Revenue) - RangeSum(Above(Sum(Revenue), 0, RowNo()))

Sum(TOTAL Revenue) gives the total future revenue (in your case, 1809).
RangeSum(Above(Sum(Revenue), 0, RowNo())) calculates the cumulative sum of revenue recognized up to the current quarter.

Expected Output:

Q1 2021: 1809 (total future revenue)
Q2 2021: 1508 (1809 - revenue recognized in Q1)
Q3 2021: 1357 (1508 - revenue recognized in Q2)
Q4 2021: 1206 (1357 - revenue recognized in Q3)
Q1 2022: 1055 (and so on...)

Graph Setup:

Use Quarter Date Start as the dimension.
Use the above expression as the measure.

Ensure your graph type is set to bar chart or another format suitable for showing cumulative values.

[Shir63]

thank you.

this is the outcome when using the formula:

Sum(TOTAL Revenue) - RangeSum(Above(Sum(Revenue), 0, RowNo()))

[Clement15]

Hello,

And does this meet your need?
Sum(TOTAL Revenue) - RangeSum(Above(Sum(Revenue), 1, RowNo()))

This is not the exact inverse of your screen but it seems consistent with your description

[Shir63]

yes it does. i marked as solution

[diegozecchini]

glad it helped