cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HMJSomerset
Helper I
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
ProviderTimestampCapacityRank
A24/05/2021 10:1811
A24/05/2021 09:1802
B24/05/2021 10:2001
A23/03/2021 10:3011
B23/03/2021 09:2021
 
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
amitchandak
Super User
Super User

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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

View solution in original post

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!