Reference RankX Value from a Summarized Table

[Roberto456]

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 SumX =

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?

[Roberto456 · Accepted solution]

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

[Roberto456]

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?

[Roberto456 · Accepted solution]

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

[Roberto456 · Accepted solution]

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

)