Dynamic “initial price” driven by a user-selected date filter

How do I calculate a dynamic “initial price” driven by a user-selected date filter?

Background

I have a receiving ledger with a few million rows of receiving entries. Each row contains the usual fields:

·         Purchase order number

·         Part number

·         Received date

·         Unit price

·         Received quantity (QTY)

Goal

I want to track price changes for each part and the resulting dollar impact over a user-selected time period. In the discovery, the user picks a start period and an end period based on the Received date.

How the calculation should work

The base (initial) price is the first price paid for a given part on or after the selected start period. Any later price change is shown as a delta from that initial price:

Price difference = Current price − Initial price

The dollar impact is then weighted by the received quantity:

$ Impact = Received QTY × Price difference

What I’ve tried

I can build every calculation without trouble except for selecting the initial price based on the user’s filter selection. Using a Lag or a previous member approach does not reproduce the result I’m after, because the initial price needs to be anchored to the first record within the selected window rather than the immediately preceding row.  Moreover, it should consider any other filter in the discover, such as the Part number selected.

My question

How can I create a variable initial price that is driven by the date range the user selects on a filter — i.e., the first unit price per part within the chosen start/end period — so the delta and impact recalculate as the filter changes?

 

Thank you 

DG