Forum Discussion
joshua1990
Post Prodigy
4 years agoCalculate last entry per Order
Hi all,
ich have a sales table that shows multiple rows for each order since there are changes. Each change is recorded with a new version number:
Order Nr
Value
Version
1
5
1...
daXtreme
Solution Sage
4 years ago
[Total Value] =
// Be careful not to filter the Sales table
// by Value or Version because the measure
// here is sensitive to filters (which is
// OK and that's how it should be). For instance,
// if you filter by Order Nr and Version, then
// the version filtered by will be effective,
// not necessarily the latest one. If you don't
// filter by Version, you'll get what you want -
// the latest version. Same for Value.
var OrdersWithLatestVersions =
generate(
values( Sales[Order Nr] ),
row(
"@LatestVersion",
calculate( max( Sales[Version] ) )
)
)
var Output =
calculate(
sum( Sales[Value] ),
treatas(
OrdersWithLatestVersions,
Sales[Order Nr],
Sales[Version]
)
)
return
Output
Here's a different method:
[Total Value] =
sumx(
generate(
selectcolumns(
values( Sales[Order Nr] ),
"@OrderNumber", Sales[Order Nr]
),
calculatetable(
topn(1,
Sales,
Sales[Version],
DESC
)
)
),
Sales[Value]
)