The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
User | Count |
---|---|
80 | |
74 | |
41 | |
30 | |
28 |
User | Count |
---|---|
108 | |
96 | |
53 | |
48 | |
47 |