I am using the inventory management power pivot template. I want to add expected date of open purchase orders in a column (like Quickbooks' built in inventory stock status report). I tried modifying an existing measure, but closest I got was displaying the expected date of the received purchase orders, not the open purchase orders.
CALCULATE(LASTDATE('INVENTORY VALUES'[Related Date]),'Document Dates'[Document Expected Date])
Any help will be greatly appreciated!
In a pivot or power pivot table, the date field is not appropriate for a measure. Doesn't mean that you can't create a calculated column, but that is a different mechanism where you add to the actual table inside of Power Pivot, but.... not necessary If I understand your need.
So this means that you can't have this date field in the middle of a list of measures. Pivot tables are just like that. Measures are things like sum, avg, max, min, of values like qty and amt. Rows and Row Labels (depending upon the version of Excel you have) are how you measure those values BY
If you want to show only open purchase orders, then use the field Document Current PO Status, and only check the Open and n/a (n/a allows other transactions that are not po to show). By default that is the setting in our out-of-the box example
As the Clearify team stated, its not normal to put dates in value column, but With PowerPivot you can create a date measure and your need is the only way to get at the Next Delivery Date.. With regular pivot tables you can not do. If you add the date Trick is to make it aggregated as you are doing. I was able to generate a date value field using this measure:
=calculate(max(vd_Job[JobTimeCreated]),vd_Job[Job Active] = "Active")
I don't have all of the Inventory field names in my head, But I think you want something like this:
=calculate(min(doc dates[expected date]),purchase order status = "Open" You want Min Date cause you want the Next Delivery.
If you list the Expected Date as a row value, there would be no way to filter just for the Min or Max date.
Hope that helps.
Choose a location