topic Re: Get most recent record from same column in DAX Commands and Tips

Get most recent record from same column

leolapa_br — Sat, 19 Aug 2023 11:15:01 GMT

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	$13.60	$13.26
ABEV3	04/Jun/2021	Sale	150	$17.91	$13.26
ABEV3	28/Jun/2021	Purchase	62	$16.01	$16.01
ABEV3	29/Oct/2021	Sale	62	$15.62	$16.01
ALSO3	07/Jan/2021	Purchase	39	$25.12	$25.12
ALSO3	03/Feb/2021	Sale	39	$27.20	$25.12
ALSO3	29/Jun/2021	Purchase	36	$27.53	$27.53
ALSO3	07/Jan/2022	Purchase	56	$17.85	$21.64
ALSO3	30/Mar/2022	Sale	92	$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?

Re: Get most recent record from same column

tamerj1 — Sat, 19 Aug 2023 18:21:32 GMT

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

Re: Get most recent record from same column

leolapa_br — Sat, 19 Aug 2023 20:35:00 GMT

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

Re: Get most recent record from same column

tamerj1 — Sat, 19 Aug 2023 21:42:51 GMT

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