locked
PowerPivot calculated field RRS feed

  • Question

  • I have an Access Table that contains 4 fields.
    ProcessDate
    PartNumber
    ReleaseDate
    Qty

    I have 1 part number with qty per ProcessDate but I have several different ProcessDates that may or may not contain the same partnumber.

    I need to be able to create a field in PowerPivot that will look for the the latest ProcessDate for that part number that has a qty and use that in the table instead of what I currently have, which is the Max value of the Qty field.


    Thursday, October 18, 2012 4:38 PM

Answers

All replies

  • What you need is something similar to the LastNonEmpty and we have 2 blog posts on it. Can you check what is the required one and see if you can get the answer?

    http://road-blogs.blogspot.ca/2012/06/lastnonempty-in-tabular-mode-part-1.html

    http://javierguillen.wordpress.com/2012/06/09/lastnonempty-in-tabular-mode-part-2-last-ever-non-empty-calculations-in-dax/

    If you are not able to find it from the posts above, let me know and I will try to get the exact formula for you. (Also it would be great if you could upload a sample file with the data so that we can test our formulas)


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    • Proposed as answer by Elvis Long Tuesday, October 23, 2012 10:53 AM
    • Marked as answer by Elvis Long Friday, October 26, 2012 5:33 AM
    Friday, October 19, 2012 11:29 AM
    Answerer
  • How do I post sample data?

    I want to clarify a few things.
    I do have an access database with 4 fields.
    ProcessDate
    PartNumber
    ReleaseDate
    Qty
    Each batch imported has a process date that contains release dates, part numbers and quantities.
    we get a new batch everyday.
    if the quantity has changed for a part number,up or down, then the part number will show up with the adjusted qty.
    if no change then the part number for that release date will not show up in the new batch

    for example
    process date 1-1-12 has a part number of 1234 and a release date of 1-10-12 and qty of 10
    ...

    Next process date 1-2-12 has a part number of 1234 and a release date of 1-10-12 and qty of 15
    ...

    Next process date 1-3-12 has no part number of 1234
    ...
    Next process date 1-4-12 has a part number of 1234 and a release date of 1-10-12 and qty of 5
    What I need to show up is the qty of 5.

    Monday, November 26, 2012 2:43 PM