Rank by Date within Group

amulder

New Member
Joined
Oct 28, 2016
Messages
20
I am trying to create a DAX calculation in PowerPivot that will give me a rank based upon a date within a group. Here is a sample of my data:

Event# Unit Arrive_Time
1234 100 2017-01-01 10:00:00
1234 102 2017-01-01 10:01:00
2345 100 2017-01-01 10:05:00
2345 102 2017-01-01 10:04:00

My desired results would show the following:

Event# Unit Arrive_Time Arrival_Order
1234 100 2017-01-01 10:00:00 1
1234 102 2017-01-01 10:01:00 2
2345 100 2017-01-01 10:05:00 2
2345 103
2345 102 2017-01-01 10:04:00 1

The Arrival_Order is what I am trying to achieve but have been unsuccessful using both the Rankx and the countrows functions as I can't seem to get the numbering to reset based on Event Number. I will also have scenarios that will not include an Arrive_Time at all. In these cases I don't want it included in the Arrival_Order.

Any help is appreciated.

Thanks!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi again,

Something like this should work.
I called your table Data.
It is a slight modification of the SQLBI version, modified to ignore blanks and base the Sequence # at 1. Also your table has a DateTime column whereas SQLBI used separate Date & Time columns.

Code:
Sequence by Event # = 
IF (
    NOT ( ISBLANK ( Data[Arrive_Time] ) ),
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                Data,
                ALLEXCEPT ( Data, Data[Event#] ),
                NOT ( ISBLANK ( Data[Arrive_Time] ) )
            ),
            Data[Arrive_Time] < EARLIER ( Data[Arrive_Time] )
        )
    )
        + 1
)
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top