Forum Discussion
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 | $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?
3 Replies
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'.
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] )
