Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
RodrigoEmiliano
Regular Visitor

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

Hi,

I have this page:

RodrigoEmiliano_1-1642600359372.png

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".

 

 

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
v-angzheng-msft
Community Support
Community Support

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.

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.