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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
leolapa_br
Resolver I
Resolver I

Get most recent record from same column

Let's say I have a table that looks like this:

 

TickerDateTransactionSharesPriceAverage Cost
ABEV301/Mar/2021Purchase77$12.93$12.93
ABEV304/May/2021Purchase73$13.60$13.26
ABEV304/Jun/2021Sale150$17.91$13.26
ABEV328/Jun/2021Purchase62$16.01$16.01
ABEV329/Oct/2021Sale62$15.62$16.01
ALSO307/Jan/2021Purchase39$25.12$25.12
ALSO303/Feb/2021Sale39$27.20$25.12
ALSO329/Jun/2021Purchase36$27.53$27.53
ALSO307/Jan/2022Purchase56$17.85$21.64
ALSO330/Mar/2022Sale92$22.61$21.64

 

Whenever there is a row with a PURCHASE the measure under the "Average Cost" column will recalculate a new average cost for that particular ticker.

 

And whenever there is a row with a SALE all the measure needs to do is to pick up the latest calculated average cost for that same ticker from that same column.

 

Every way I tried to do that I incur circular dependency error on my DAX code.

 

How can I do that while avoiding circular dependency?

3 REPLIES 3
tamerj1
Super User
Super User

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

@tamerj1 not sure why but I keep getting the error on the first VAR CurrentDate "A single value for column 'Date' in table 'fTrans' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.".

 

And in case you ask, yes the dataset has a calendar table whose date column has a one-to-many relationship to the date column under the fact table 'fTrans'.

@leolapa_br 

Ok I mistakenly thought that it was a calculated column. For a measure please try

Average Cost =
VAR CurrentDate =
MAX ( 'Date'[Date] )
VAR CurrentTickerTable =
CALCULATETABLE ( 'fTrans', REMOVEFILTERS (), VALUES ( 'fTrans'[Ticker] ) )
VAR FilteredTable =
FILTER (
CurrentTickerTable,
'fTrans'[Date] <= CurrentDate
&& 'fTrans'[Transaction] = "Purchase"
)
VAR LastPurchaseBefore =
TOPN ( 1, FilteredTable, 'fTrans'[Date] )
RETURN
SUMX ( LastPurchaseBefore, 'fTrans'[Price] )

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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