I wondering if anyone can help me modify the below expression
MAX ( SELECT (LOCAL CATALOGUE PRICES[List Price], [Product Name & Reference] = [_THISROW].[Product Name & Reference]))
I have a date column called [Valid From Date] in the same "LOCAL CATALOGUE PRICES" table
How do I adjust this expression to return value of "List Price" from the row that has the max value in the [Valid From Date] column?
I believe I might need to use the MAXROW function, but I not sure how to and if it is the correct function to use.
So essentially what I trying to do is make sure I always return the latest "List Price" value based on the latest [Valid From Date] value as it not always the case that the MAX [List Price] value is always the latest value.
Hope I making sense.
Is the "List Price" a key column?
Hi @AleksiAlkio so I have a computed key for Table: Local Catalogue Prices called [_ComputedKey]
Let me know if can perhaps direct me on the correct expression.
I have a virtual column called [Price List] that is a price column type in another table called Table: Local Product Assortment, where I was using the following expression
MAX ( SELECT (LOCAL CATALOGUE PRICES[List Price], [Product Name & Reference] = [_THISROW].[Product Name & Reference]))
Which was sufficient at the time as I did not have more than one price for the relevant [Product Name & Reference].
However I am adding additional prices for the same [Product Name & Reference] and sometimes we are droping a price. So I can not longer use the MAX expression to return the max [List Price] as it might not be the latest price list.
So I need to use [Valid Unit Data] to ensure I pull the latest price.
The below screenshot might give you a better example of what I want to achieve.
User | Count |
---|---|
37 | |
30 | |
29 | |
20 | |
18 |