Forum Discussion
Resolver IIGet most recent record from same column
Resolver IItamerj1 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'.
Community ChampionOk 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] )