Need help with VAR based DAX

[safifaruqi]

Hi Guys, 

I have the following data from a table called "Module Sales" with the following columns: Transaction Type, Document Number, Baseref where row level data could look like
Transaction Type = Invoice, Document Number = 244485, Baseref = 523620
Transaction Type = Delivery note, Document Number = 523620, Baseref = 140064
Transaction Type = Sales Order, Document Number = 140064, Baseref = 250926
Transaction Type = Quote, Document Number = 250926, Baseref = null how can i use the baseref associated with one transaction

The first document number in our case is generated at the time for furnishing a quote when the said baseref is null...as document converts from a quote to a sales order transaction type, previous document number is the breadcrumb that falls under baseref for the currend document number.

I want to create a calculated column that cycles through the selected document number with transaction type "invoice" to retrieve the cusomter delivery date recorded at the time of "sales order" we want to measure our own on-time delivery against the promised delivery date. 

my current dax 

calculated baseref =

VAR SelectedInvoice = SELECTEDVALUE('Module Sales'[document number])
VAR CurrentRow = CALCULATE(FIRSTNONBLANK('Module Sales'[baseref], 1),
FILTER('Module Sales',
'Module Sales'[Document Number] = SelectedInvoice))

Return CurrentRow

doesnt let me build on the above and just blanks out when I try to use the "current Row" variable output as a filter to find the "document number" where the "transaction type" is "delivery note" and "document number" = "current row" output. 

 

Any help would be greatly appreciated ! 

Thanks guys.