Getting Measure to Display as Constant in Context

[BrianJ]

@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.

• Brian
  eDNA Forum – Department Activity solution.pbix (3.0 MB)

[BrianJ]

@adsa,

OK, thanks to a very creative and elegant solution on the above problem by @melissa, we are unstuck and back in business. This ended up being a pretty complex and challenging problem, due to the fact that the nature of the fiscal year selection in the slider creates two distinct cases, and the solution has to address both dynamically. Case 1 is simpler - the user selects a fiscal year range including the minimum fiscal year in the data set. In Case 2, the user does not select the minimum fiscal year, potentially creating a carryover situation. The graphic below illustrates how I structured the solution based on these two cases.

Many of the measures I created in a solution were simple date harvesting measures, capturing the endpoints of the bracketed areas in the graphic above. Here’s a screenshot that shows all the values
of the different date measures, the Carryover measure and the resulting "Buffer"and “Buffer with Carryover” results for the selected date range.

You can see all of the measure code in the attached solution file, but here are the two measures that do most of the heavy lifting:

Melissa’s carryover measure:

Carryover = 
VAR MinFY =  [Harvest FY Slicer Min] 
VAR MaxFY = [Harvest FY Slicer Max] 

VAR vTable1 =
    FILTER( ALL( Dates ),
        Dates[Fiscal Year] >= MinFY &&
        Dates[Fiscal Year] <= MaxFY
    )

VAR CumulCO1 =
    SUMX (
        vTable1,
        [Net Clnk]
    )

VAR vTable2 =
    FILTER( 
        ALL( Dates ),
        Dates[Fiscal Year] <= MaxFY
    )

VAR CumulCO2 =
    SUMX (
        vTable2,
        [Net Clnk]
    )

RETURN 
CumulCO2 - CumulCO1

And then the Buffer with Carryover measure:

Buffer w Carry =

VAR vTable =
ADDCOLUMNS(
    DATESBETWEEN(
        Dates[Date],
        [Min Selected Date],
        [Max Selected Date]
    ),
    "vBuffer", [Buffer],
    "vCO2", [Carryover]
)

VAR vBuf =
CALCULATE (
    [Buffer] + [Carryover],
    FILTER ( 
        ALLSELECTED ( Dates ), 
        Dates[Date] <= MAX ( Dates[Date] )
    )
)

RETURN vBuf

I hope this is helpful.

• Brian
  eDNA Forum – Department Activity solution w Carryover.pbix (3.0 MB)

P.S. @EnterpriseDNA team - @melissa should be credited with the solve on this thread. I was already credited on the initial thread linked to this one. She provided the new analysis that solved the specific problem posted in this thread. Thanks.

[adsa]

@BrianJ @Melissa

Thank you to you both for looking into this query and assisting with a solution.

I can confirm that I have implemented your solution and is working well.

[BrianJ]

@adsa,

Thanks for the update, and for your patience. Glad to hear the solution is working well for you.

Enjoyed working with you and @Melissa on this one - had no idea initially that this one would prove as challenging as it did, but learned a lot in the process.

• Brian