DAX Depletion Rate

[slimthugster]

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!

[slimthugster]

See the above example in Excel

[v-chuncz-msft]

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

[MattAllington]

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. 

[slimthugster]

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]

[MattAllington]

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.