How Find Last price with supplier and item wise......

[Samank75]

My Table

GRNNO   GRNDate   Supplier    Item     Price

A1       2019/01/05     Sup1      Item1        100
A2       2019/01/10     Sup2      Item2      150
A3       2019/01/15     Sup1      Item1       120
A3       2019/01/15     Sup1      Item4       200
A4       2019/01/16     Sup3      Item4       130
A5       2019/01/20     Sup2       Item2      135

I need find last price with supplier and Item wise. How can I do it......?

[FrankAT]

Hi @Samank75 

take a look at the following solution:

Last Price item wise = 
CALCULATE(
    MAX('Table'[Price]),
    FILTER(
        ALLEXCEPT('Table','Table'[Item]),
        MAX('Table'[GRNDate]) = 'Table'[GRNDate]
    )
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Samank75]

Hi @FrankAT 

Thank you for your code...... When I run this, I can't see some price..... why ?

 

[FrankAT · Accepted solution]

Hi @Samank75 

in my answer Last Price is a measure not a calculated column!

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Samank75]

Why not working to me......

 

[amitchandak]

@Samank75 , Try like

calculate(lastnonblankvalue(table[GRNDate],max(Table[Price])) , allexcept(Table, Table[Supplier],Table[Item]))

or

calculate(lastnonblankvalue(table[GRNDate],max(Table[Price])) , filter(allselected(Table), Table[Supplier] = max(Table[Supplier]) && Table[Item]= max(Table[Item])))

[Samank75]

Sorry not working

[amitchandak]

@Samank75 , Please find the file after the signature . Let me know output needed,if it does not work

[Samank75]

I use this one.

Last Date = VAR SP=Query1[Supplier]

return

MAXX(

FILTER(ALL('Query1'),'Query1'[Supplier]=SP),

'Query1'[GRNDate])

--------------------------------

Its give result , But its not Item wise......

[Samank75]

Result should be

GRNNO   GRNDate   Supplier    Item     Price

A3       2019/01/15     Sup1      Item1       120
A3       2019/01/15     Sup1      Item4       200
A4       2019/01/16     Sup3      Item4       130
A5       2019/01/20     Sup2       Item2      135

[amitchandak]

@Samank75 , GEN no also need to a measure now

File attached after signature