cancel
Showing results for
Did you mean:
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

## 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

## Re: DAX Depletion Rate

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.
Frequent Visitor

## Re: DAX Depletion Rate

See the above example in Excel

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

+
[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

Highlighted
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.