Forum Discussion

leolapa_br's avatar
leolapa_br
Icon for Resolver II rankResolver II
3 years ago

Get most recent record from same column

Let's say I have a table that looks like this:   Ticker Date Transaction Shares Price Average Cost ABEV3 01/Mar/2021 Purchase 77 $12.93 $12.93 ABEV3 04/May/2021 Purchase 73 $...
"DAX"
Circular dependency
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi leolapa_br 

please try

Average Cost =
VAR CurrentDate = 'Table'[Date]
VAR CurrentTickerTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Ticker] ) )
VAR FilteredTable =
FILTER (
CurrentTickerTable,
'Table'[Date] <= CurrentDate
&& 'Table'[Transaction] = "Purchase"
)
VAR LastPurchaseBefore =
TOPN ( 1, FilteredTable, 'Table'[Ticker] )
RETURN
MAXX ( LastPurchaseBefore, 'Table'[Price] )