Forum Discussion
Helper IVCumulative total by year
Hi Guys,
I need to calculate the cumulative total of sales (& other metrics like projected sales, planned sales, etc ) which I need to show online and stacked column chart. I have a single select-year slicer at the top so the cumulative sum should be done for a year. e.g. Dec'22 should be the sum of Jan'22-Dec'22 sales and Jan'23 should only be Jan'23. Can someone please help me create a measure to achieve this?
Here is what my data looks like:
| MONTH | Year | Sales |
| 2022-01-01 | 2022 | 11 |
| 2022-02-01 | 2022 | 22 |
| 2022-03-01 | 2022 | 28 |
| 2022-04-01 | 2022 | 37 |
| 2022-05-01 | 2022 | 52 |
| 2022-06-01 | 2022 | 61 |
| 2022-07-01 | 2022 | 69 |
| 2022-08-01 | 2022 | 79 |
| 2022-09-01 | 2022 | 86 |
| 2022-10-01 | 2022 | 115 |
| 2022-11-01 | 2022 | 122 |
| 2022-12-01 | 2022 | 143 |
| 2023-01-01 | 2023 | 119 |
| 2023-02-01 | 2023 | 90 |
| 2023-03-01 | 2023 | 80 |
Hi itsmeanuj ,
If I understand correctly, the issue is that you cumulative total by year. Please try the following methods and check if they can solve your problem:
1.Create the Table 2 and Data Table.
2.Move the Table 2 month field to the slicer visual.
3.Create a measure to cumulative totals.
Measure = CALCULATE( SUM('Table 2'[Sales]), FILTER( ALL('Data Table'), 'Data Table'[Year] = MAX('Data Table'[Year]) && 'Data Table'[MONTH] <= MAX('Data Table'[MONTH]) ) )4.Move the month to the stacked column chart X-axis and move the measure to the Y-axis.
5.Select the Dec 22 and the result is shown below.
Best Regards,
Wisdom Wu
2 Replies
Hi itsmeanuj ,
If I understand correctly, the issue is that you cumulative total by year. Please try the following methods and check if they can solve your problem:
1.Create the Table 2 and Data Table.
2.Move the Table 2 month field to the slicer visual.
3.Create a measure to cumulative totals.
Measure = CALCULATE( SUM('Table 2'[Sales]), FILTER( ALL('Data Table'), 'Data Table'[Year] = MAX('Data Table'[Year]) && 'Data Table'[MONTH] <= MAX('Data Table'[MONTH]) ) )4.Move the month to the stacked column chart X-axis and move the measure to the Y-axis.
5.Select the Dec 22 and the result is shown below.
Best Regards,
Wisdom Wu
- lbendlin
Super User
Please look into Quick Measures. They include templates for the Running Total calculation.
