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