Max date by group, rewrite measure to create calculated column

leolapa_br — Mon, 28 Aug 2023 20:12:59 GMT

I have the following calculated table from this code:

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

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:

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):

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.

Re: Max date by group, rewrite measure to create calculated column

leolapa_br — Mon, 28 Aug 2023 20:25:55 GMT

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] )

topic Max date by group, rewrite measure to create calculated column in DAX Commands and Tips

Max date by group, rewrite measure to create calculated column

Re: Max date by group, rewrite measure to create calculated column