Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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...
Need Help
Show and Tell
Tips and Tricks
jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago


      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

      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        7 years ago

        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.