Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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:
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
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.
@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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |