Value on last occurence

[bmpi]

I need a measure that gives me the quantity of the last order line (max line number) per ORDERNUMBER.

For order S45879412 the max measure should return 70, and for S45879555 return 75.

[amitchandak]

@bmpi , I have blog on the same topic

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

Last Status = var _max = maxx(filter(ALLSELECTED(Data), Data[Ordernumber] = Max(Data[Ordernumber])), Data[Line]) 
return 
CALCULATE(Sum(Data[quantity]), filter((Data) ,  Data[Line] =_max))

[bmpi]

Thank you that worked!

Must admit, I don't quite get  how that first part works.

Last Status = var _max = maxx(filter(ALLSELECTED(Data), Data[Ordernumber] = Max(Data[Ordernumber])), Data[Line]) 

If I write it like this, I still get it to work:

var _max = maxx(Data, Data[Line])  

Can you explain the difference? (it is probably a good reason :))

[amitchandak]

@bmpi , The second one will work if do not take ID ,

I Check and did not work the same for me

Also, you need one additional measure to get the correct grand total

Last Status = Var _max = maxx(filter( ALLSELECTED(Data), Data[Ordernumber] = max(Data[Ordernumber]) ),Data[Line])
return
CALCULATE(sum(Data[quantity]), filter( (Data), Data[Ordernumber] = max(Data[Ordernumber]) && Data[Line] =_max))

Sum Last Qty = sumx(VALUES(Data1[Ordernumber]) , [Last Qty])

[bmpi]

I actually do not get the sum correct.

I get 76 when I should get 43.

My testdata looks like this:

Order		Line		QTY
45879412		1		5
45879412		2		8
45879555		2		19
45879555		1		5
45879333		2		5
45879333		3		2
45879444		1		5
45879444		3		14
45879444		2		5

Formula looks like this:

SumQty =

VAR Tablemax = MAXX(FILTER(ALLSELECTED(Sheet1), Sheet1[Order] = MAX(Sheet1[Order])),Sheet1[Line])

VAR LastLine = CALCULATE(SUM(Sheet1[QTY]),FILTER(Sheet1, Sheet1[Line] = Tablemax && Sheet1[Order] = MAX(Sheet1[Order])))

VAR SumQty = SUMX(VALUES(Sheet1[Order]),LastLine)

RETURN SumQty

[v-shex-msft · Accepted solution]

HI @bmpi,

You can try to use the following measure formula if it helps:

SumQty =
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE ( ALLSELECTED ( Sheet1 ), [Order], "LastLine", MAX ( Sheet1[Line] ) ),
        "LastLineQTY",
            CALCULATE (
                SUM ( Sheet1[QTY] ),
                FILTER (
                    ALLSELECTED ( Sheet1 ),
                    Sheet1[Line] = [LastLine]
                        && Sheet1[Order] = [Order]
                )
            )
    )
RETURN
    SUMX ( summary, LastLineQTY )

Regards,

Xiaoxin Sheng