Maximum of Cumulative Sums in a dynamic date range
I have a problem where I need to be able to display the maximum cumulative sum within a selected date range.
Data would look like the following, except we would have multiple departments as well:
Date
Department
Daily Net
6/3/2018
A
50
6/4/2018
A
60
6/5/2018
A
10
6/6/2018
A
-50
6/7/2018
A
20
As such, the Cumulative sum values if this date range (6/3/2018-6/7/2018) were selected would be:
50,
110,
120,
70,
90
I need to be able to show in a table the below (but it would have multiple departments):
Department
Max Cumulative Sum
A
120
B
50
The Date range should be able to be changed dynamically, and the calculation would need to dynamically change depending on the selected range.
I have looked into the RangeSum and Before functions, but my table does not include the date column, so i don't think that would work. The other option would be set analysis, but I'm not sure how to make it cumulative since in the examples I have seen, the dates are hardcoded or a static variable.
I did not end up using this solution, but thank you! The reference the article made to Aggr tables made me realize that I didn't fully understand how Aggr worked. After learning that, i was able to solve with Range Sum
I should mention I stated the date format incorrectly originally - the format I have is YYYY/MM/DD, so the Text sort used in my solution conveniently works.
I did not end up using this solution, but thank you! The reference the article made to Aggr tables made me realize that I didn't fully understand how Aggr worked. After learning that, i was able to solve with Range Sum
I should mention I stated the date format incorrectly originally - the format I have is YYYY/MM/DD, so the Text sort used in my solution conveniently works.
