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).
