Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
leolapa_br
Resolver II
Resolver II

Max date by group, rewrite measure to create calculated column

I have the following calculated table from this code:

 

Tbl = 
SUMMARIZECOLUMNS( 
	dAssets[Ticker], 
	dDates[Date], 
	"Purchases", [Shares purchased], 
	"Sales", [Shares sold] 
)

 

Tbl.png 

 

Then I created the following measure that retrieves the last purchase date prior to each sale row...

 

Last purchase date before respective sale = 
VAR Ticker_Ref = MAX( Tbl[Ticker] )
VAR Date_Ref = MAX( Tbl[Date] )
RETURN
	MAXX( 
		FILTER( 
			ALLSELECTED( Tbl ), 
			[Ticker] = Ticker_Ref && 
			[Date] <= Date_Ref && 
			[Purchases] <> BLANK() 
		), 
		[Date] 
	)

 

... which works just fine when dropped to a table visual:

Tbl_Visual.png

 

I tried to produce those same results above as a calculated column but I keep getting wrong results. How can I get that to work?

 

Since I'm a DAX newbie, I still have a hard time reproducing a measure code as a calculated column or vice-versa...

 

The closest I got was this...

 

Last purchase date before respective sale = 
VAR Date_Ref = MAX( Tbl[Date] )
RETURN
	CALCULATE( 
		MAX( Tbl[Date] ), 
		ALLEXCEPT( Tbl, Tbl[Ticker] ), 
		Tbl[Date] <= Date_Ref, 
		Tbl[Purchases] <> BLANK() 
	)

 

... but it returns wrong results (max purchase row date per group of tickers, instead of max purchase row date within each group of tickers prior to or up to each respective sale row date):

Tbl_Calc_Column.png

 

And before one suggests I should think long and hard before resorting to calculated columns as opposed to measures: I've already done such philosophical conundrum and realized that for this particular application, which is producing that piece of calculation, and others that follow, in order to reach a particular (and very complex) calculation goal, I have no other choice but go with calculated columns.

1 ACCEPTED SOLUTION
leolapa_br
Resolver II
Resolver II

Managed to get to the bottom of this thing...

Last purchase date before respective sale = 
VAR Ticker_Ref = Tbl[Ticker]
VAR Date_Ref = Tbl[Date]
RETURN
	MAXX( 
		FILTER( 
			ALL( Tbl ), 
			Tbl[Ticker] = Ticker_Ref && 
			Tbl[Date] <= Date_Ref && 
			Tbl[Purchases] <> BLANK() 
		), 
		Tbl[Date] 
	)

View solution in original post

1 REPLY 1
leolapa_br
Resolver II
Resolver II

Managed to get to the bottom of this thing...

Last purchase date before respective sale = 
VAR Ticker_Ref = Tbl[Ticker]
VAR Date_Ref = Tbl[Date]
RETURN
	MAXX( 
		FILTER( 
			ALL( Tbl ), 
			Tbl[Ticker] = Ticker_Ref && 
			Tbl[Date] <= Date_Ref && 
			Tbl[Purchases] <> BLANK() 
		), 
		Tbl[Date] 
	)

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.