Spare parts replacement date offseting

[emersonmora]

Hello,

We are currently working on a dasboard regarding maintenance of equipment fleet.

I have a table (FACT_Partes1) with the following fields (showing data for just one equipment) regarding spare replacement: (sorted by date)

FACT_Partes1

I'm trying to offset the date for each component (such dates is when the part is replaced) in order to get an "initial date" and the offseted date will be the "change date" (fecha cambio parte). Later with the initial date and change date I will read from another table the operation hours of such equipment and thus calculate the service life such component had.

I was partially able to do so by using a new function called "OFFSET" using the following code:

Fecha cambio parte = 
CALCULATE(
    [Fecha inicial parte],
    OFFSET(
        1,
        ALLSELECTED(FACT_Partes1),
        ORDERBY(FACT_Partes1[Fecha], ASC)
    )
)

If I select only one equipment and one spare part, I get the result I want:

Filtering single part

Last Work order should have an empty change date since the part has not been yet replaced.

BUT if I do no select any particular spare part, I get undesired results as shown below:

No part filtered

Clearly the OFFSET function is working since it is offseting the original table dates (which is sorted by date).

The question here is how can I "Isolate" (or group by) every spare part in order to have only change dates for such part and last change date to show empty? See mockup picture below to see desired result:

Desired result

Thank you for your advice.

[lbendlin · Accepted solution]

OFFSET only works on the current visual (or filter context if you want).  In order to look up "previous" data you are better off using an index column in your data source.

[emersonmora]

Yes, you are right. 

Thanks to your advice I took a different approach:

1. Sorted spare parts table by equipment, then by spare part code and then by date.
2. Gave this table and index column
3. Then added a calculated column: 

Fecha de cambio de parte = 
IF (
    LOOKUPVALUE (
        FACT_Partes1[SCO],
        FACT_Partes1[Index],  FACT_Partes1[Index] 
    )
        = LOOKUPVALUE (
            FACT_Partes1[SCO],
            FACT_Partes1[Index],  FACT_Partes1[Index]  + 1
        )
        && LOOKUPVALUE (
            FACT_Partes1[Equipo],
            FACT_Partes1[Index],  FACT_Partes1[Index]
        )
            = LOOKUPVALUE (
                FACT_Partes1[Equipo],
                FACT_Partes1[Index],  FACT_Partes1[Index]  + 1
            ),
    LOOKUPVALUE (
        FACT_Partes1[Fecha],
        FACT_Partes1[Index],  FACT_Partes1[Index]  + 1
    ),
    MAX(FACT_Indicadores[Fecha])
)  

This achieved the desired result.

Thanks a lot