Calculate last entry per Order

[joshua1990]

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
1	4	2
2	9	1
3	7	1

Now I would like to calculate the sum [Value] for each order with latest version.

How would you do that? Using MAXX und Sum?

[tamerj1]

Hi @joshua1990 

please try

Sum of Last Version =
SUMX (
    VALUES ( 'Table'[Order Nr] ),
    CALCULATE ( MAXX ( TOPN ( 1, 'Table', 'Table'[Version] ), 'Table'[Value] ) )
)

[daXtreme]

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