How to reset a running cumulative measure?

[Anonymous]

Hi,

I am working on an analysis tool that provides information on water usage from a storage pond. There are 3 primary variables that are evaluated as a function of time

1. Pond Capacity - barrels of water
2. Riser Rate - barrels of water that can be added to the pond daily
3. Draw Down Rate - barrels of water that are consumed from the pond on a daily basis

The draw down and refresh rates should be triggered based on construction events that are evaluated by a start date and end date.

So as an example, we have a pond that has the following details:

1. Pond Capacity = 4 million barrels of water
2. Riser Rate = 15,000 barrels per day
3. Draw Down Rate (variable rate depending on construction event) = 66,000 barrels per day.

The problem I am having is the measure I created to plot this water usage doesn't seem to reset back to the capacity over time when I have multiple construction events plotted, as you can see below:

Each drawdown is essentially a new construction event with a provided Start Date, End Date, and Draw Down Rate. When a new construction event starts, the Water Pond volume should equal the instantaneous volume at the end date of the last event + the number of days between events times your refresh rate

Here are the measures used to calculate the line:

Cumulative Refresh Volume = 
VAR test = 
CALCULATE(
    SUMX(
        FILTER(
            ALLSELECTED('Report Dates'[Date]),
            'Report Dates'[Date] <= MAX('Report Dates'[Date]) && [Daily Fresh Water (bbl)]>0
        ),
        RISERS[Refresh Rate (BPD)]
    )
)
Return
IF(
    ISBLANK([Daily Fresh Water (bbl)]),
    0,
    test
)

and for the drawdown

Cumulative Fresh Water Volume = 
var fresh_water = CALCULATE(
    [Total Fresh Water (bbl)],
    FILTER(
        ALLSELECTED('Report Dates'[Date]),
        'Report Dates'[Date] <= MAX('Report Dates'[Date]) && 'Report Dates'[Date]>=[Frac Start Date]//&& [Daily Fresh Water (bbl)]>0
    )
)
return 

IF(
    [Daily Fresh Water (bbl)] > 0,
    fresh_water,
    0
)

I then take the Total Pond Capacity and do the following:

Remaining Pit Volume = 

[Total Pit Capacity] + [Cumulative Refresh Volume] - [Cumulative Fresh Water Volume]

As a secondary note: i'd also like to ideally calculate the minimum time needed to refill the water pond to maximum capacity prior to the next construction event

Here is a link to my PBI model: https://github.com/rstover/PowerBI

[Greg_Deckler]

Will try to take a closer look but this might help in the mean time:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211

[Anonymous]

@Greg_Deckler did you happen to have a chance to look at this?