# Assistance with isolating the latest sales date from a table by Account

• ### Question

• All...

I have a table of Account Data that also contains Sales Dates...  I need to isolate the 'latest' Sales date for an Account and use that Date as a comparison for further filtering...  here is an example data set -

From the following I need to isolate only the latest row for each Account, I have 'ranked' the rows based on the Sales Date - I have played with RANKX, but I have not been successful, perhaps I am over thinking this - Assistance is greatly appreciated in advance

 Account#1 April-17 1 Account#1 March-17 2 Account#1 February-17 3 Account#1 January-17 4 Account#2 December-16 1 Account#2 November-16 2 Account#2 October-16 3 Account#3 February-17 1 Account#3 January-17 2 Account#4 September-16 1

joemac

Monday, April 10, 2017 5:46 PM

• Hello,

In 2014 Analysis Services we are still limited to build our table expression logic through ADDCOLUMNS/SUMMARIZE . No Groupby

By the way, for the most recent row my measure will have to be re-written like below.

```DEFINE
MEASURE Table1[MaxAccDate] =
CALCULATE ( MAX ( Table1[Sales Date] ), ALLEXCEPT ( Table1, Table1[Account] ) )
EVALUATE
CALCULATETABLE (
SUMMARIZE ( Table1, Table1[Account], Table1[Sales Date] ),
FILTER ( Table1, Table1[Sales Date] = [MaxAccDate] )
)
ORDER BY Table1[Account]```

• Marked as answer by Thursday, April 13, 2017 11:32 AM
Tuesday, April 11, 2017 8:23 PM

### All replies

• Hello,
Here is one way how to solve it as a calculated column.
**** Account and new calculated columns both go on Rows. Filter out the blanks with Row Filters

```=
SWITCH(
TRUE(),
Table1[Sales Date] <> CALCULATE (
MAX ( Table1[Sales Date] ),
ALLEXCEPT ( Table1, Table1[Account] )
),
Table1[Sales Date]

)```

Here is also a query version. Just in case

```DEFINE
MEASURE Table1[MaxAccDate] =
CALCULATE ( MAX ( Table1[Sales Date] ), ALLEXCEPT ( Table1, Table1[Account] ) )
EVALUATE
CALCULATETABLE (
SUMMARIZE ( Table1, Table1[Account], Table1[Sales Date] ),
FILTER ( Table1, Table1[Sales Date] < [MaxAccDate] )
)
ORDER BY Table1[Account]```

Monday, April 10, 2017 9:51 PM
• Thank you for the reply Nick -

What I'm really interested in is to capture this set of rows as a table...  If I were to do what I am interested in constructing I could use a SQL row_number () over (partition by Account order by SalesDt desc) as in the image below - is there a method to 'filter' the table (as a subset) to only include the most recent row from a given field... There may be a capability in the 'new' GroupBy function, but I am limited by the version of the SSAS server (2014) in the client environment.

﻿

joemac

Tuesday, April 11, 2017 7:05 PM
• Hello,

In 2014 Analysis Services we are still limited to build our table expression logic through ADDCOLUMNS/SUMMARIZE . No Groupby

By the way, for the most recent row my measure will have to be re-written like below.

```DEFINE
MEASURE Table1[MaxAccDate] =
CALCULATE ( MAX ( Table1[Sales Date] ), ALLEXCEPT ( Table1, Table1[Account] ) )
EVALUATE
CALCULATETABLE (
SUMMARIZE ( Table1, Table1[Account], Table1[Sales Date] ),
FILTER ( Table1, Table1[Sales Date] = [MaxAccDate] )
)
ORDER BY Table1[Account]```

• Marked as answer by Thursday, April 13, 2017 11:32 AM
Tuesday, April 11, 2017 8:23 PM