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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
leolapa_br
Resolver I
Resolver I

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 I
Resolver I

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 I
Resolver I

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors