Time Normalize

[hemikicker]

Greetings all,

I'm a PowerPivot & DAX novice but I'm loving the functionality it has provided me so far.

So what I have is a table with oil production data for various wells:

Date,WellID,OilVolume

There are several different wells and their oil production starts on different dates. Its common in my industry to look at the performance of the wells in a time-normalized format to compare the wells performance with its peers.

So instead of looking at production by absolute date:

****
Date Well1 Well2 Well3
1/1/2012 200
2/1/2012 187 150
3/1/2012 178 205 140
4/1/2012 182 185 130
5/1/2012 168 130 135
****

I'd want to normalize the time dimension:

****
Month Well1 Well2 Well3
1 200 205 150
2 187 185 140
3 178 130 130
4 182 135
5 168
****

I envision achieving this as a measure in PowerPivot:

****
Date,WellID,OilVolume,NormalizedMonth
****

The way I thought would be easiest would be a formula to find the minimum date filtered by the whatever well is in the row. The subtract that by from the date.... I just cannot get the syntax right...

Date WellID OilProduction NormalizedMonth
1/1/2012 Well1 200 1
2/1/2012 Well1 187 2
3/1/2012 Well1 178 3
3/1/2012 Well2 205 1
4/1/2012 Well2 185 2
.
.
.

Thanks in advance for your help!

 

[scottsen]

Must be a common area for power pivot work, as I have spent quite a few hours consulting other oil drillers

Just to make sure I am understanding you... What you want is... on a per well basis, the number of months (+1) since the well first came online?

In a calculated column, I would think something like...
=CALCULATE(ROUND(([SomeDate]- MIN([SomeDate])) / 30, 1) + 1, FILTER(TheTable, TheTable[WellId] = Earlier(TheTable[WellId])))

For each row in the table, filter it to other rows with the same Well Id... and do some funky divide by 30 math to get the number of months. And add 1.

 

[srdirect]

I just found this posr which should help you cluster the wells

Compare product performance after launch « PowerPivotPro