cancel
Showing results for
Did you mean:
Helper I

## Daily Rank Entries from Customers

I have a table where providers submit their capacity each day. Sometimes, if a new slot opens up they complete the form a second time to update their capacity. What I want to do is rank each entry for each provider each day.
e.g
 Provider Timestamp Capacity Rank A 24/05/2021 10:18 1 1 A 24/05/2021 09:18 0 2 B 24/05/2021 10:20 0 1 A 23/03/2021 10:30 1 1 B 23/03/2021 09:20 2 1

Daily Rank =
RANKX(
FILTER(
ProvCapacity,
ProvCapacity[ProviderID]=EARLIER(ProvCapacity[ProviderID])
),
ProvCapacity[Timestamp]
)

With this formula, I get the correct rank for 24/05/2021 but 23/05/2021 is assigned a rank of 3.
How can I adjust the expression to reset the calculation for each day?
1 ACCEPTED SOLUTION
Super User IV

@HMJSomerset , Create a date from from Timestamp

Date = [Timestamp].date
or
Date = date(year([Timestamp]),month([Timestamp]),day([Timestamp]))

and then try this rank column

Daily Rank =
RANKX(
FILTER(
ProvCapacity,
ProvCapacity[ProviderID]=EARLIER(ProvCapacity[ProviderID]) && ProvCapacity[Date]=EARLIER(ProvCapacity[Date])
),
ProvCapacity[Timestamp]
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

2 REPLIES 2
Super User IV

@HMJSomerset , Create a date from from Timestamp

Date = [Timestamp].date
or
Date = date(year([Timestamp]),month([Timestamp]),day([Timestamp]))

and then try this rank column

Daily Rank =
RANKX(
FILTER(
ProvCapacity,
ProvCapacity[ProviderID]=EARLIER(ProvCapacity[ProviderID]) && ProvCapacity[Date]=EARLIER(ProvCapacity[Date])
),
ProvCapacity[Timestamp]
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper I

Thank you.  I have amended the DAX and it now works correctly.

Announcements

#### Manage your user group events

Check out the News & Announcements to learn more.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

#### Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors