none
Rank a column with a date range condition

    Question

  • Hi,

    I have a simple two column table - Date of Visit and Party code.  I would like to write a calculated column formula in the Power Pivot window which will rank the date of visit column for each party within a certain date range.

    In this file (http://sdrv.ms/GWcWCg), you may read the question, see my expected answer and also see my workaround (which returns an incorrect answer).

    Could you kindly correct my mistake in the calculated item formula.

    Thank you for your help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, October 14, 2013 12:52 AM

Answers

  • This seems not a rank calculation.

    You have to add a Date table to the data model and create a relationship between your table and the date table.

    Once you do that, you can create two calculated columns: StartPeriod and Answer - see definition below.

    You can download an example here: http://sdrv.ms/1ckEhIv<style type="text/css">.fctbNone{ color:#000000; } </style>

    StartPeriod = 
    NEXTDAY ( 
    DATEADD ( ENDOFYEAR ( Dates[Date],
    "31-03", ), - 2, YEAR
    ) )
    Answer = 
    COUNTROWS ( 
        FILTER ( 
            ALL ( Table1 ), 
            Table1[Date of visit] 
                > EARLIER ( [StartPeriod] ) 
                && Table1[Date of visit] 
                    <= EARLIER ( Table1[Date of visit] ) 
                && Table1[Party code] 
                    = EARLIER ( Table1[Party code] ) 
        ) 
    )

    • Marked as answer by Ashish Mathur Monday, October 14, 2013 11:50 PM
    Monday, October 14, 2013 3:05 PM

All replies

  • This seems not a rank calculation.

    You have to add a Date table to the data model and create a relationship between your table and the date table.

    Once you do that, you can create two calculated columns: StartPeriod and Answer - see definition below.

    You can download an example here: http://sdrv.ms/1ckEhIv<style type="text/css">.fctbNone{ color:#000000; } </style>

    StartPeriod = 
    NEXTDAY ( 
    DATEADD ( ENDOFYEAR ( Dates[Date],
    "31-03", ), - 2, YEAR
    ) )
    Answer = 
    COUNTROWS ( 
        FILTER ( 
            ALL ( Table1 ), 
            Table1[Date of visit] 
                > EARLIER ( [StartPeriod] ) 
                && Table1[Date of visit] 
                    <= EARLIER ( Table1[Date of visit] ) 
                && Table1[Party code] 
                    = EARLIER ( Table1[Party code] ) 
        ) 
    )

    • Marked as answer by Ashish Mathur Monday, October 14, 2013 11:50 PM
    Monday, October 14, 2013 3:05 PM
  • Hi,

    Thank you for replying.  I think the Dates Power Pivot tab can be avoided if the formula in StartPeriod can be changed to

    =EDATE([Date of visit],-24)

    The Answer column can now have the following formula

    =COUNTROWS (FILTER ( ALL ( Table1 ), Table1[Date of visit] > EARLIER([StartPeriod]) && Table1[Date of visit] <= EARLIER ( Table1[Date of visit] ) && Table1[Party code] = EARLIER( Table1[Party code] ) ) )
    Thank you for pointing me the right direction.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, October 14, 2013 11:50 PM