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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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