Cummulative Total With Inbuilt Dimension Filter (Rangesum with If?)
I have racked my brains with this one and figure it's time I asked the forum experts for help! I am trying to produce a line graph that shows cumulative sales against cumulative budget (which I can achieve using the rangesum function). However, I want to build in a dimension restriction for which weeks are shown in the graph without the user filtering manually. Here is some example data together with the two styles of graph I would like to achieve:-
The first graph I want to produce merely limits the weeks displayed to a specific max point, already calculated in a separate variable (vMaxWeek), which in this example is WeekNo 4. The second graph I want to produce covers the entire sales season (up to vMaxSeasonWeek) showing the cumulative budget for the whole season, but actuals only for the part season (to vMaxWeek) - otherwise it looks like sales are forecast to flatline for future weeks.
Note - In my actual examples the time period has a lower limit too that I also want to auto-restrict that links to the start of the season (vStartSeason).
I attach an excel file with the data above and examples of the two graphs I'd like to produce. Any help/suggestions would be hugely appreciated. This has hit the limit of my QV skills!