Forum Discussion
Most recent value by material by customer
Im trying to calculate my company's list prices by customer but, because our data isn't strictly a relational table (other sales info) many of these are repeated and depending on the date, the prices are different. Theyre also sometimes (mistakenly) different by customer. In order to get the most recent list price by material, I did this.
MaxDate = CALCULATE(MAX('Book'[BookDate]),FILTER('Book, 'Book'[MaterialNo]=EARLIER('Book'[MaterialNo])))
I found this here.
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Finding-the-most-recent-value/m-p/117481#M211
However, this only gets me so far. If I filter by customer level it only shows materials where the last bookings happened to be by that customer. If two customers sold the same product, the material would only show under the customer that sold it last.
Ideally, Id like the max book date by material for each customer. How would I adapt the code above for that?
7 Replies
Hello Anonymous
Give this a try
Max Book Date = VAR Customer = SELECTEDVALUE(Book[Customer]) VAR Material = SELECTEDVALUE(Book[MaterialNo]) RETURN CALCULATE( MAX(Book[BookDate]), FILTER( ALL ( Book[BookDate], Book[Customer], Book[MaterialNo]), Book[Customer] = Customer && Book[MaterialNo] = Material ) )*modified a bit so we are not filtering the entire book table.
You will need to delete your first calculated column in order for the new one to calculate. Also, I did mine so it would work as a measure. You woull not need the SELETEDVALUE in the variables if you are doing it as a calculated column.
