Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone!
I have trouble calculating the cumulative sum of some amounts. I have line items with Amount and Date as shown below.
Date | Amount |
6-May-19 | 812 |
30-May-19 | 963 |
17-Jun-19 | 108 |
25-Jun-19 | 266 |
4-Aug-19 | 617 |
28-Aug-19 | 565 |
26-Oct-19 | 507 |
14-Nov-19 | 544 |
28-Dec-19 | 570 |
Now, I want to plot a line chart of the cumulative monthly sum of these values. I assume that would involve two operations: 1st to add up line items for every month to get a monthly total and 2nd to do a cumulative sum of these monthly values. Also, It should show values only for a certain period, for ex May-2019 to Dec-2019.
It should look something like the attached chart-
Can someone please suggest a formula to achieve this?
If you want the line chart to show only the sum of a [Field] per month you need to have a dimension with a [Month] or [YearMonth]. I would suggest adding these fields in the script editor to the table MasterCalendar. Then, add one of the month grouped field as a dimension to the line chart and you standard expression, sum([Amount]).
In the dimension section of the line chart you can also put: year(Date) & '-' & month(Date).
Hope it helps!