In a chart, cumulative sum not persisting for a selected date range
I need some help in calculating the cumulative sum which persists the value of the whole date range and display the same value for a selected range as well.
We need to measure “ongoing subscriptions” count which is sum of previous day's ongoing subscriptions and current date subscriptions.
For day#1 Ongoing subscription is same as Subscription count.
Screenshot#1 Cumulative sum for the whole date range
Screenshot#2 Cumulative sum for the selected date range. Desired value is in highlighted color below which is calculated separately and added to the screenshot
Screenshot#3: Cumulative sum for the entire date range for the selected filter
Screenshot#4: Cumulative sum of the selected date range and the filter. Desired result is highlighted and added in the below screenshot.
I would greatly appreciate if someone can help me with this problem?
FYI... I tried other approached like
Calculate the cumulative total at SQL level and roll it to the QVW but the issue is we never know what filters the user will be selecting. In the sample document I attached, I have only 3 filters but in my original copy, I have 7 different filters for the user to select
A single group by date would not work because, it will through off all the filters from the equation