Calculated column varying result for each row depending on dates and item within the same table

[MarceloSaez]

Hello guys! hope you're doing all well.

Here is a tricky one (at least for my modest knowledge):

I have a set of items (oil-producing wells - Well), and each one of them has an oil production history (Oil produced - qoil, in each month - monthly Date). So my data looks like this:

Well              Date                qoil
ME-0001       01/08/2020     1.5
ME-0001       01/09/2020     1.5
ME-0001       01/10/2020     1.4
ME-5000       01/08/2020     4.4
ME-5000       01/09/2020     4.0
ME-5000       01/10/2020     3.7
ME-3251       01/08/2020     0.2
ME-3251       01/09/2020     0.2
ME-3251       01/10/2020     0.2

(note that this is just a sample, being the real data thousands of wells and all dates from the 1950s to the present date).

The evolution of these oil production curves usually follows an exponential form, such as the following:

Mathematically speaking, this curve is modeled as:

if in the Date = t0 --> qoil0 = Qo0

then in the next Date =t1 --> qoil1 = Qo0 x Exp (-DNA/12)

where DNA is the Annual Nominal Decline rate, which in the equation is divided by 12 because DNA is Annual, and the period is in months.

My data is obtained from measurements in an Oil Field, so I have a production history curve for each Well, and it shows a certain noisy behavior due to inherent methods of measurements (and many other reasons). As it is a historical curve, all the dates are Past Dates (they begin around 1950 and they continue to being added to my Data set every month), being the latest available date equal to October-2020. Based on these data points, I want to calculate the DNA value for each well, by looking at the latest 24 dates (2 years back).

Basically, the method for estimating DNA is:

• Transform qoil into Log(qoil) to have a linear plot and perform a linear regression of these values.

• Once obtained the Slope and Intercept values of this linear regression, you calculate the Log(qoil) both for the target Date Log(qoil_end), and for the date 24 months before Log(qoil_ini), i.e.
  • Log(qoil_ini) = [TargetDate minus 24 months] x Slope + Intercept
  • Log(qoil_end) = [TargetDate] x Slope + Intercept
• Transform the Log values into qoil:
  • qoil_ini = 10^(Log(qoil_ini))
  • qoil_end = 10^(Log(qoil_end))
• Calculate DEA = (qoil_ini - qoil_end) / qoil_ini
• Calculate DNA = - LN (1 - DEA) * 12 / 24

I have managed to calculate this DNA value for each well, but only using the latest available Date as TargetDate (so I have the Nominal Decline rate of the latest 24 months). I have done that by filtering my auxiliary calculated columns using MAX(data[Date]) function, as in the following picture:

Note that for a given Well, all the rows have the same numeric value for the calculated DNA column, that is because, for that particular well, all the calculated formulas use the same TargetDate as a starting point (which is Maximum date, or latest historical date)

In the following link is the *.pbix containing what I have done already.

Test Decline.pbix 

Here is my request:

• I want to estimate DNA value for every date. That is, standing on any date, look back 24 months, and estimate DNA of the previous 24 months period.
• In other words, for a given well, all the rows (all the Dates in the history of a well) should have a different DNA value, because every time, the calculations should use different periods of 24 months. All the rows in the complete table should have different values (because for each row you have different Wells and/or different Dates).

The aim is to be able to know at any time in history which was the Decline Rate of a particular well. I need to use DAX (not Power Query) to solve it.

Your help will be much appreciated. Thanks in advance for your kind support, I've been struggling with this problem for a couple of weeks so far. Best regards!

[MarceloSaez]

@amitchandak, in the following link you can find:

• Desired output table: a sample table of the desired output (one distinct value of DNA for each row). For instance, I obtained the values for Sep-2020 by deleting the Oct-2020 data from the source data, so MAX(date[Date]) refers to Sep-2020.
• Source file: the source original data file (excel file),
• the pbix (you can find this file in my original post).

There is no sensitive data as all the data contained in these files is not real.

Thank you so much for your interest.

Regards, 

[MarceloSaez]

Hi Ibendlin, thank you for your response.

In the aim of simplifying the problem, I showed an example of a Well having a near-perfect exponential production history, but this is far from being the common case: Producing wells often show very complex changes of trends in their production, sometimes increasing and sometimes decreasing. The causes of these changes are many, such as:

• Techniques used to maintain or even increase oil reservoir rock pressure (i.e. water injection) can produce increments (negatives DNAs).
• When these techniques suffer a failure, the DNAs can change drastically to double the previous value.

The following image is real data of a well, where the variability of DNA can be observed.

Then, the entire production history of a well can not be characterized by a single DNA value. Instead, DNAs are estimated all across the life of a producing well, because is a measure of the well is performing in comparison with expectations, or other similar wells.

The problem I'm trying to solve is related to how to use each row date as a parameter, instead of using MAX(data[Date]).

Thanks again for your help.

Best regards,

Marcelo

[amitchandak]

@MarceloSaez Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[lbendlin]

That sounds overly complex. Why not do this entirely in the logarithmic world?  All you need to know is the logarithmic slope and the current value.  Everything else can be computed, no?