Calculate difference between consecutive rows - memory error

[Anonymous]

Hello,

I am trying to calculate the difference between chronological rows in my table but I'm getting a memory error whenever I try to execute the DAX command. The table (AAP - exits) is 45000 rows and the other table referenced (AAP) is 80000. AAP - exits is just a filtered version of AAP but if I change the references to all be to AAP - exits I get a circular reference error. My machine has 8 GB RAM.

Column 2 = CALCULATE(MAX(AAP[Timestamp]),AAP[IMO]=EARLIER('AAP - exits'[IMO]),AAP[Timestamp]<EARLIER('AAP - exits'[Timestamp]))

I've tried other variations of the formula, including DATESBETWEEN instead of the inequality because I read on here that can be more efficient. But EARLIER doesn't work within DATESBETWEEN and I cannot find another way to refer to the current row. 

Would be open to solutions in DAX or Power Query. 

Thanks for your time.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I suggest you to try this code to create a calcualted column,

Column 2 =
CALCULATE (
    MAX ( AAP[Timestamp] ),
    FILTER (
        AAP,
        AAP[IMO] = EARLIER ( 'AAP - exits'[IMO] )
            && AAP[Timestamp] < EARLIER ( 'AAP - exits'[Timestamp] )
    )
)

If you code will still return memory error, please refer to this offical blog to optmize your data model.

Best Regards,
Rico Zhou

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

[Anonymous]

Thank you, this worked!