topic Re: Calculate last entry per Order in DAX Commands and Tips

Calculate last entry per Order

joshua1990 — Thu, 29 Sep 2022 13:56:42 GMT

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?

Re: Calculate last entry per Order

daXtreme — Thu, 29 Sep 2022 14:17:40 GMT

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

Re: Calculate last entry per Order

tamerj1 — Thu, 29 Sep 2022 17:30:43 GMT

Hi joshua1990

please try

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