cancel
Showing results 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.

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

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...

``````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.

1 ACCEPTED SOLUTION
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]
)``````
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]
)``````

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors