Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

  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:

 

Water Usage.png

 

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 3
v-kelly-msft
Community Support
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,
Kelly
Did I answer your question? Mark my post as a solution!
Anonymous
Not 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.

Anonymous
Not applicable

@v-kelly-msft any luck?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.