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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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