Forum Discussion
Resetting a measure based on a trigger date
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
- Pond Capacity - barrels of water
- Riser Rate - barrels of water that can be added to the pond daily
- 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:
- Pond Capacity = 4 million barrels of water
- Riser Rate = 15,000 barrels per day
- 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
3 Replies
- v-kelly-msft
Community Support
Hi Anonymous ,
I'm not quite sure whether I have caught your point,could you pls share me some sample data and advise me what your expected output is?
Much appreciated.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!- AnonymousNot applicable
v-kelly-msft I don't know if this is the best way, but I posted my PBI model here: https://github.com/rstover/PowerBI
Let me know if you have any trouble this way. If this doesn't work, I can load some dummy data.
- AnonymousNot applicable
v-kelly-msft any luck?