Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Last unit price

Hello all I'm trying to demonstrate the last cost price of a product on my sales table. I've add a column with this : Latest UP = CALCULATE( SUM('Shops Products'[Un Price]), FILTER('Shops...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    If you are looking as a measure

     

    Measure = 
VAR __id = MAX ( 'Shops Products'[Product#] )
VAR __date = CALCULATE ( MAX( 'Shops Products'[date] ), ALLSELECTED ( 'Shops Products' ),  'Shops Products'[Product#] = __id ) 
RETURN CALCULATE ( MAX ( 'Shops Products'[Un Price] ), VALUES ( 'Shops Products'[Product#] ), 'Shops Products'[Product#] = __id, 'Shops Products'[date] = __date )

     

    As column

    Max Date = maxx(filter('Shops Products','Shops Products'[Product#] =earlier('Shops Products'[Product#])),'Shops Products'[Date])
Max Price = maxx(filter('Shops Products','Shops Products'[Product#] =earlier('Shops Products'[Product#]) && 'Shops Products'[Date#] =earlier('Shops Products'[Max Date ])),'Shops Products'[Un Price])

     

amitchandak's avatar
amitchandak
Super User
6 years ago

If you are looking as a measure

 

Measure = 
VAR __id = MAX ( 'Shops Products'[Product#] )
VAR __date = CALCULATE ( MAX( 'Shops Products'[date] ), ALLSELECTED ( 'Shops Products' ),  'Shops Products'[Product#] = __id ) 
RETURN CALCULATE ( MAX ( 'Shops Products'[Un Price] ), VALUES ( 'Shops Products'[Product#] ), 'Shops Products'[Product#] = __id, 'Shops Products'[date] = __date )

 

As column

Max Date = maxx(filter('Shops Products','Shops Products'[Product#] =earlier('Shops Products'[Product#])),'Shops Products'[Date])
Max Price = maxx(filter('Shops Products','Shops Products'[Product#] =earlier('Shops Products'[Product#]) && 'Shops Products'[Date#] =earlier('Shops Products'[Max Date ])),'Shops Products'[Un Price])

 

celen's avatar
celen
Frequent Visitor
2 years ago

Hi amitchandak , how should the measure be written when I have several years of data in my table and I want to calculate the last unit price from the different years? Last unit price in 2022, last unit price in 2023 and so on...