YoY Daily Production BOE when the first year is a partial Year

[Candace Hays]

I want to calculate the YoY% on BOE/D in a cross table. I am able to calculate YoY% by using the following formula:

Sum([s815] + ([s816] / 6) + [s819]) / Sum([s815] + ([s816] / 6) + [s819]) OVER (Intersect(Previous([Axis.Columns]))) as [YoY% BOE]

S815 is Net Oil

S816 is Net Gas

S819 is Net NGL

However, this formula does not take into account the number of days in the year. It assumes the same number of days in each year. On a monthly basis, the MoM% works fine because the number of days are similar, but the first year in the YoY%, the number is inflated. In the picture below, Net MBOE for 2020 is 8.89 and 5.93 for 2019. The YoY% should be 1.50 not 2.25

The dataset has multiple wells with the same timeframe. To get the unique number of days in a year, I used the following formula:

UniqueCount([OUTDATE]) * 30.42 as [Num Days].

 

And combined this with the Net MBOE calculation. and it works fine.

Sum(([s815] / 1000) + ([s819] / 1000) + ([s816] / 6 / 1000)) / (UniqueCount([OUTDATE]) * 30.42) as [Net MBOE]

However, When I tried to use the over function to YoY, I get an error saying it is invalid.

(Sum(([s815] / 1000) + ([s819] / 1000) + ([s816] / 6 / 1000)) / (UniqueCount([OUTDATE]) * 30.42)) / (Sum(([s815] / 1000) + ([s819] / 1000) + ([s816] / 6 / 1000)) / (UniqueCount([OUTDATE]) * 30.42)) over (Intersect(Previous([Axis.Columns])))

[Khushboo Rabadia]

You can use over with THEN keyword as post-aggregation approach:

(Sum(([s815] / 1000) + ([s819] / 1000) + ([s816] / 6 / 1000)) / (UniqueCount([OUTDATE]) * 30.42)) THEN [Value]/First([Value])over (Intersect(Previous([Axis.Columns])))

[Candace Hays]

Thank you it worked.