# PowerPivot calculated field

• ### 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

### 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://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

• Proposed as answer by Tuesday, October 23, 2012 10:53 AM
• Marked as answer by Friday, October 26, 2012 5:33 AM
Friday, October 19, 2012 11:29 AM
• 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