cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slimthugster Frequent Visitor
Frequent Visitor

DAX Depletion Rate

I am struggling to figure out how to perform a rolling DDA rate table.

 

The calculation I am attempting goes as follows:

 

Part 1: [PPE Beginning of Month] + [Capital Additions] - [DDA] = [PPE End of Month] 

 

I keep hitting a circular dependency because [DDA] formula is as follows:

 

[DDA Rate]= [Total Proved Reserves Beggining of Month]/[PPE Beginning of Month]

and

[DDA] = [DDA Rate] * [Production of Barrels of Oil Equivalent]

 

The problem is the ciruclar dependency between your DDA which you are using to deplete your PPE and the monthly (or quartlerly) recalcultion of the rate.

 

Please help I have sunk so many hours into trying to figure this one out and I am at my wits end!

 

 

5 REPLIES 5
Super User
Super User

Re: DAX Depletion Rate

Are you trying to do this in calc columns?  Without me making any judgement as to whether this is the best approach or not, you should be able to remove circular references by writing a single calc column. Just substitute the formulas from the interim columns directly into the final column. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Community Support Team
Community Support Team

Re: DAX Depletion Rate

@slimthugster,

 

It would be better if you could illustrate with some sample data. To have a better understanding of circular dependency, you may take a look at this link.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
slimthugster Frequent Visitor
Frequent Visitor

Re: DAX Depletion Rate

DDA Example.PNG

 

See the above example in Excel

 

 

slimthugster Frequent Visitor
Frequent Visitor

Re: DAX Depletion Rate

I have tried both calculated columns and Dax Measures. I keep running into the same issue.

 

Step 1 Beginning of Period Balance

 

PPE Drilling (BOP):=calculate(sum([Capital Additons (CapEx)])+[PPE Addition Unidentified]+[DDA],all(Oklahoma),filter(all(tblDate),tblDate[BOMonth]< Max(tblDate[BOMonth])))
+
[Beginning PPE]

 

Step 2 Beginning of Period Reserves

 

Reserves PDP (BOP):=calculate(sum([PDP Additions])+[EUR Add Unidentified]+sum([SEC Reserve Changes])+sum([BOE Production])+[BOE Prod Unidentified],all(Oklahoma),filter(all(tblDate),tblDate[BOMonth]< Max(tblDate[BOMonth])))
+
[PDP Reserves Start]

 

Step 3 Drilling Rate

 

Drilling Rate:=if(sum([Toggle])=1,calculate([PPE Rate Calc (BOP)]/[Reserves Rate Calc (BOP)]),1)

 

Step 4 Depletion

 

DDA:=sumx(OKLAHOMA,([BOE Prod Exisiting]+[BOE Prod Unidentified])*[Drilling Rate])

 

Step 5 Ending Balances

 

PPE Drilling (EOP):=calculate(sum([Capital Additons (CapEx)])+[PPE Addition Unidentified]+[DDA],all(Oklahoma),filter(all(tblDate),tblDate[BOMonth]<= Max(tblDate[BOMonth])))+[Beginning PPE]

 

 

 

Super User
Super User

Re: DAX Depletion Rate

See my previous post. The problem should be solved by creating a single calc column instead of lots of interim columns. Start in the last column. For each reference to an earlier column, replace the column ref with the actual contents of the column. Repeat until it works. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.