Measure to Get previous row data based on filter and others columns

[RodrigoEmiliano]

Hi,

I have this page:

On Each row we have a production register, where "Nome" is the name of the product, "data" is the date when it was made and "hora" is the time that it was made, "maquina" corresponds to the machine where the product was made.

I want to calculate a measure of the average time that takes it takes to make a product based on the columns "Nome" and "maquina" in a determined period of time delimited by the column "data".

The average time would be the average of the subtraction: "hora" - "previous time" of each row value  where "previous time" would be a measure to get the previous "hora" that has the same "Name", "maquina" and the same "data".

[v-angzheng-msft · Accepted solution]

Hi, @RodrigoEmiliano 

Try this:

Get previous =
CALCULATE(
    MAX( 'table'[hora] ),
    FILTER(
        ALLEXCEPT( 'table', [Nome], [maquina], [data] ),
        'table'[id] < MAX( 'table'[id] )
    )
)

Average time =
AVERAGEX( 'table', MAX( 'table'[hora] ) - [Get previous] )

Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wieengels]

This solution just solved a problem I've been sitting on all day - and much cleaner than anything I've tried! Thank you!