Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Reference RankX Value from a Summarized Table

Hello Everyone.   I'm trying to find the 2nd invoice Date created by customer in my database and I'm having a hard time doing this with RankX.   I need to use RankX because it has to be dynamic. ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I have updated the formula but its still not filtering for Rank 2 and only showing the Max Date.

     

    Invoice Date (Min) R2 =

    VAR NEWTABLE = SUMMARIZE( 'Cart', 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date], "Rank" ,[Customer Invoice Rank])
     
    RETURN
     
    CALCULATE(
              GROUPBY(
                   ADDCOLUMNS( GROUPBY (NEWTABLE, 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date], [Rank]) ,"Invoice Min",[Invoice Date (Min)])
                  ,"Value",( MAXX( CURRENTGROUP(), [Invoice Min] ) )
                  )
    ,FILTER(NEWTABLE,[Rank]=2))
  • Anonymous's avatar
    Anonymous
    6 years ago

    Solved: I put the Calculate inside the addcolumns

     

    Invoice Date (Min) R2 =

    VAR NEWTABLE = SUMMARIZE('(SQL) Cart','(SQL) Cart'[Customer_ID],'(SQL) Cart'[Invoice_ID],'(SQL) Cart'[Invoice_Date],"Rank",[Customer Invoice Rank])
    RETURN
    GROUPBY(
    ADDCOLUMNS(
    GROUPBY(NEWTABLE,'(SQL) Cart'[Customer_ID],'(SQL) Cart'[Invoice_ID],'(SQL) Cart'[Invoice_Date],[Rank])
    ,"Invoice Min",CALCULATE([Invoice Date (Min)],FILTER(NEWTABLE,[Rank]=2))
    )
    ,"Value",(MAXX(CURRENTGROUP(),[Invoice Min]))
    )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello Everyone.

 

I'm trying to find the 2nd invoice Date created by customer in my database and I'm having a hard time doing this with RankX.

 

I need to use RankX because it has to be dynamic.

 

Below is the RankX formula I'm currently using:

Customer Invoice Rank =

VAR CURRENTCUSTOMER = MAX('Cart'[Customer_ID])
RETURN
RANKX( FILTER( ALLSELECTED( 'Cart'),'Cart'[Customer_ID]=CURRENTCUSTOMER),[Invoice Date (Min)],,ASC,Dense)
 
So far gotten it to SumX the Rank Values for each customer, but I really need to reference the Date for a certain Rank value.
 Rank Test =
VAR NEWTABLE = SUMMARIZE( 'Cart', 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date])
RETURN
GROUPBY(
            ADDCOLUMNS(
                              GROUPBY(NEWTABLE, 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date]) ,
                              "The Rank",[Customer Invoice Rank]
                                     )
            ,"Value" ,SUMX(CURRENTGROUP(),[The Rank])
)
 
In the end I really need it do below but I cant use Calculate in this situation: 
Min Invoice Rank 2 =
VAR NEWTABLE = SUMMARIZE('Cart', 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date])
RETURN
GROUPBY(
          ADDCOLUMNS(
                           GROUPBY( NEWTABLE, 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date]) ,
                           "Rank",[Customer Invoice Rank]
                                  )
           ,"Value",CALCULATE( MIN( 'Cart'[Invoice_Date]), FILTER( CURRENTGROUP(), [Rank]=2))
)
 
Does anyone know how to reference the Rank in this situation?