Count number of rows between transaction types

[leolapa_br]

I have a calculated table that returns each purchase and sale transaction by stock ticker by date.

Then I created a helper column that returns whether each row relates to a "Purchase" transaction, a "Sale" transaction, or to both ("Purchase/Sale").

ADDCOLUMNS( 
	SUMMARIZECOLUMNS( 
	    dAssets[Ticker], 
	    dDates[Date], 
		"Purchased", [Shares purchased], 
		"Sold", [Shares sold], 
	    "Balance", [Shares balance] 
	), 
    "Transaction", SWITCH( 
    	TRUE(), 
		[Purchased] <> BLANK() && [Sold] = BLANK(), "Purchase", 
		[Purchased] = BLANK() && [Sold] <> BLANK(), "Sale", 
		[Purchased] <> BLANK() && [Sold] <> BLANK(), "Purchase/Sale" 
	) 
)

What I need is a calculated column that indicates how many rows (NEGATIVE) above each "Sale" row the last "Purchase" sale row had taken place.

• Obviously, in case a row refers to a "Purchase" transaction then return "null/blank".
• And for those rows that refer to dates on which both "Purchase" and "Sale" took place then return ZERO.
• One requirement must be followed: making sure such row count takes place within each group of tickers.

Below is a screenshot of the table produced by the above code and I hardcoded in red the desired calculated column output.

This seems simple but I just can't get a way to make it work...

Right out of the gate I get the error "the column 'Transaction' cannot be found or may not be used in this expression" when trying to generate a calculated column with the Date of the last "Purchase" transaction prior to each respective "Sale".

VAR Tbl = 
	ADDCOLUMNS( 
		SUMMARIZECOLUMNS( 
			dAssets[Ticker], 
			dDates[Date], 
			"Purchased", [Shares purchased], 
			"Sold", [Shares sold], 
			"Balance", [Shares balance] 
		), 
		"Transaction", SWITCH( 
			TRUE(), 
			[Purchased] <> BLANK() && [Sold] = BLANK(), "Purchase", 
			[Purchased] = BLANK() && [Sold] <> BLANK(), "Sale", 
			[Purchased] <> BLANK() && [Sold] <> BLANK(), "Purchase/Sale" 
		) 
	)
RETURN
	ADDCOLUMNS(
		Tbl, 
		"Last Purchase Date", 
		VAR Ticker_Ref = dAssets[Ticker]
		VAR Date_Ref = MAX( dDates[Date] )
		RETURN
		CALCULATE( 
			MAXX( 
				dDates, 
				dDates[Date] 
			), 
			dAssets[Ticker] = Ticker_Ref, 
			dDates[Date] <= Date_Ref, 
			[Transaction] = "Purchase" || [Transaction] = "Purchase/Sale" 
		)
	)

[lbendlin]

That can be done with the windowing functions as long as you can articulate what "before"  means from a sorting perspective.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...