How can I create an index calculated column in DAX based on monthly production data?

DAX calculated column for INDEX based on two columns: Date and Item.

Hi Comunity!

I'd like to create the two calculated columns in DAX at the right of the following data:

Explanation: I have several Items called "Wells". For each one of them, I have monthly data (oil, water, and gas production). For each well I'd like to find the first production data > 0, and from then start to count an Index column.

• The first column "RunningActMonth" would count every time one of the production columns is greater than zero, but it will repeat the earlier index if the production = 0.
• The second column "RunningMonth" would keep indexing forever after the First production data.

Your help will be much appreciated.
Regards from Argentina.

Create a column to output whether there is production.

Production = table[water] > 0 || table[oil] > 0 || table[gas] > 0

We can reference that in our two columns.

RunningActMonth = 
Calculate( 
    distinctcount( table[date] ), 
    Filter(
        allexcept( table, table[well] ), 
        Table[date] <= earlier( Table[date] ) && [production]
    )
)

This one counts the number of distinct dates with any production at the selected well on or prior you the current date.

RunningMonth = 
Max( 0, 
    Datediff(
        Calculate( 
            Firstnonblank( table[date], if( [production], true(), blank ()) ),
            allexcept( table, table[well] )
        ),
        Table[date],
        Month
    ) + 1
)

This computes the first date with a production value greater than zero. It then takes the non-negative month difference between the current month and that month.

---EDIT-------------------------

To make this scalable, we can do the same thing with measures:

Production = IF( SUM ( table[water] ) = 0 || SUM ( table[oil] ) = 0 || SUM ( table[gas] ) = 0, TRUE(), BLANK() )

I updated production to evaluate to BLANK() instead of FALSE() for zero-production intersections to speed up evaluation later.

We will need a Calendar (Date) table linked to the production table for this next bit to work.

RunningActMonth = 
CALCULATE( 
    COUNTROWS( 
        FILTER( 
            DATESBETWEEN( 'Calendar'[Date], BLANK(), LASTDATE( 'Calendar'[Date] ) ),
            [Production]
        )
    ),
    ALLEXCEPT( table, table[well] )
)

RunningMonth = 
Max( 0, 
    DATEDIFF(
        CALCULATE( 
            FIRSTNONBLANK( 'Calendar'[date], [Production] ),
            ALLEXCEPT( table, table[well] )
        ),
        LASTDATE( 'Calendar'[Date] ),
        Month
    ) + 1
)

The only major changes here are referencing the Calendar table, which will speed up evaluation. Essentially, this will iterate over wells & dates. Your model speed will be determined by the number of wells X the number of dates in your Calendar.