How to calculate lengths by the same order number

[Anonymous]

This is the table that I have and I'm trying to make a measure that does (NewLength - 200Length)/200 length for the same order number.

The desired output for the measure with this dataset is 0.02289.

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create measure like DAX below.

Measure1 = 
var _PrevDate = CALCULATE(MAX('Table'[Date]), FILTER( ALLEXCEPT('Table', 'Table'[Number]), 'Table'[ Date] <  MAX('Table'[ Date])))
var _PrevNewLength = CALCULATE(FIRSTNONBLANK('Table'[NewLength], 1),  FILTER( ALLEXCEPT('Table', 'Table'[Number]), 'Table'[ Date]= _PrevDate))
RETURN
DIVIDE(_PrevNewLength- MAX('Table'[ 200 length]), MAX('Table'[ 200 length]), 0)

Best Regards,

Amy 

Community Support Team _ Amy

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

[az38]

Hi @Anonymous 

try a measure

Measure = 
var _NewLength = MAX(Table[NewLength])
var _Length200 = CALCULATE(MAX(Table[Length]), ALLEXCEPT(Table, Table[Number]), Table[Code] = 200)

RETURN
DIVIDE(
(_NewLength - _Length200 ),
_Length200
)