Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
Solved! Go to Solution.
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])
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])
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...
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!
Cool!! Thank you so much!!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |