Home Forums Power Pivot Select latest value that differs from zero

This topic contains 3 replies, has 2 voices, and was last updated by  tomallan 11 months, 2 weeks ago.

Viewing 4 posts - 1 through 4 (of 4 total)
• Author
Posts
• #9153

kingofhkb
Participant
• Started: 3
• Replies: 6
• Total: 9

Hi

I am having a bit of an issue, where i want to select the latest price that differes from zero.

I have three tables, Calendar, Inventory and Purchaseline. The calendar an inventory tables is connected to the Purchaseline table. The Purchaseline table consists  of 5 column and I would like a measure that returns the latest value of Purchaseline[Exchange_rate] * Purchaseline[Price in currency] for at given Inventorynumber, that is different from zero.

I have attached an Example workbook. And I am using Excel 2016

Regards

Christian Andersen

###### Attachments:
You must be logged in to view attached files.
#9162

tomallan
Keymaster
• Started: 7
• Replies: 2552
• Total: 2559

Christian,

Years ago, I worked for a manufacturing company that purchased many inventory items. It would be possible, on the last date an item was purchased, there could be multiple purchases of the same item with different purchase amounts. Reasons for these different amounts on the same date could be driven by purchasing from different vendors or purchasing with different shipping/receiving requirements or purchasing from different locations.

Maybe none of cases above apply to your business, but if they do, and when there are multiple non-zero purchases of the inventory item on the same “last date”, which one would you want to list in the pivot table: the greatest amount or the least amount or an average amount (or would you be interested in separate measures for each)? Of course, the greatest, least or average amounts would be based on converted currency.

Please see attached workbook for some ideas.

Also, if there are millions of rows in your data, you could have a more performant measure by creating a calculated column (before importing into Power Pivot) that multiplied exchange rate and price in currency.

Tom

###### Attachments:
You must be logged in to view attached files.
#9166

kingofhkb
Participant
• Started: 3
• Replies: 6
• Total: 9

Thanks a lot Tom

The measure with the greatest amount works perfectly for my case. It is only a few cases where I actually need the measure, for the most part I know the exact value of the inventory unit.

It is used to give a rough estimate of our inventory value, and they would rather it is a bit to high than to low

Regards

Christian

#9169

tomallan
Keymaster
• Started: 7
• Replies: 2552
• Total: 2559

Christian,

Thanks for the insight.

Looking forward to working with you again in the PowerPivotPro forums,

Tom

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.