locked
Assistance with isolating the latest sales date from a table by Account RRS feed

  • 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

Answers

  • 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 joemac130 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 joemac130 Thursday, April 13, 2017 11:32 AM
    Tuesday, April 11, 2017 8:23 PM