@adsa and I have been working on this one for a couple of days now and have gotten stuck right at the goal line, and could really use a fresh pair of eyes taking a look.
You can read the background on this thread here. It’s an interesting problem – basically calculating a cumulative total of a net measure prior to an ALLSELECTED date range (call that prior cumulative total “carryover”), then calculating the cumulative total of the measure over the ALLSELECTED date range (call that cumulative total “buffer”), and finally adding the carryover and buffer measures together.
Here’s the screenshot that illustrates the problem we’re having:
Everything is working properly dynamically based on the fiscal year slicer (unfortunately, the data is such that the only year that has carryover > 0 is 2019, so to get the problem to show up you need to slide both min and max sliders to 2020, where carryover =1). As you can see in the screenshot, with no evaluation context everything calculates as it should (green box). The problem is that I need to get the carryover total to populate in every row of the ALLSELECTED date range (red box). Basically, I want the measure to behave like a constant over this selected date range. I’ve thrown every filter context manipulation I can think of at it, and I can’t get this one last thing to work.
PBIX file attached. Any help getting us over the goal line on this one would be greatly appreciated.
eDNA Forum – Department Activity solution.pbix (3.0 MB)