Most recent value by material by customer

[Anonymous]

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?

[jdbuchanan71]

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.

[Anonymous]

circular
I had to change the names but its the same deal. Got an error "circular dependency"

[jdbuchanan71]

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.

[Anonymous]

It did work as a measure but I think I need it to be a calculated column. Thanks for all the help thus far this is sort of out of my depth haha

[Cmcmahan]

Just change the top to this:

VAR Customer = SELECTEDVALUE('Book for Promos'[BuyingGroupName])
VAR Material = SELECTEDVALUE('Book for Promos'[MaterialNo])

When you're creating a measure, any time you name a column, it comes back as the entire column of data (even if that column is only one row).  SELECTEDVALUE([column]) gives you a single value of the first thing in the column.

When creating a calculated column, naming a column returns a single value, since DAX knows it's working with one point of data.

[jdbuchanan71]

Do you have multiple calculated columns in your table?  If so, that can cause problems when you try to add one that is based on the table you are calcing against (like we are).  Can you tell us a bit more about what you are trying to accomplish?  There might be another way to get to the same answers.

[Anonymous]

Im trying to get list prices for our material numbers so that I can then make a "sales margin calculator." However, the only table I can get the list and costs from is a sales table. Unfortunately, this is not a relational table and due to the many instances customers have bought the same product, I need to take the list price from the last transaction. When I did this with the old calculated column it worked but it created an issue where only the last transaction per material would show rather than the last transaction per material by customer.

There are no other calculated columns on the table.