cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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 Products','Shops Products'[Date]
= CALCULATE(
MAX('Shops Products'[Date]),
FILTER('Shops Products', 'Shops Products'[Product#] = EARLIER('Shops Products'[Product#]))
)
&& 'Shops Products'[Product#] = EARLIER('Shops Products'[Product#])
))

The weird part is that it doesn always work...
Pedro
1 ACCEPTED SOLUTION
Super User

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])``````

5 REPLIES 5
Super User

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])``````

Frequent Visitor

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...

Frequent Visitor

Hi @amitchandak and @FrankAT  , i have this issue. can you help me?

I got a table named Product Price that has
Product ID        Date                 Price

1                  01/01/2020      20
1                  02/01/2020      21
2                  02/01/2020      2
2                  03/01/2020      3

and in other table i need a calculated column that brings me the last price of the product.

Product Id    Last Price

1                  21
2                   3

I've seen other that worked but using a measure, i need the column. Thanks!

Anonymous
Not applicable

Cool!! Thank you so much!!

Community Champion

Hi @Anonymous ,

if you use a calculated column it will only be calculated after time of creation and when you refresh the report, until than it stays, it's not dynamically.

Regards FrankAT

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors