Sort a text column by a date column, and get today's date

[cliang]

Hello all!

I have a table with two columns, 'Student Status' (Active, Leave, Repeat, Independent Study, etc.) and 'Start Date of Status'. 

'Start Date of Status' has future dates as well as past dates so I can't sort by latest date (because it would be in the future). 

I want to get the 'Student Status' according to the current date and show the date that the status started. 

When I try to Sort by column from 'Student Status' to 'Start Date of Status', it gives me an error of:

"We can't sort the 'Student Status' column by 'Start Date of Status'. There can't be more than one value in 'Start Date of Status' for the same value in 'Student Status'. Please choose a different column for sorting or update the data." 

Any help would be much appreciated!

-Christian

[cliang · Accepted solution]

I ended up finding a solution on YouTube for what I was looking for!

Here's the video for reference:

https://www.youtube.com/watch?v=hidJ5T_DYQ0

[v-zhangti]

Hi, @cliang 

Is this the problem you are experiencing?

You can try the following methods.
Column:

Rank = RANKX(FILTER('Table',[Student ID]=EARLIER('Table'[Student ID])),[Start Date of Status],,DESC)

Sort the Student status columns in the order of the Rank column. Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cliang]

Yes, that is the error I am experiencing.

And, no I don't think that's the result I am looking for. I'm trying to get the most recent status according to the current date. For example, today is 11/4/2022, I want to get the status according to 11/4/22. 

Hope this makes sense! Thank you for your help

[v-zhangti]

Hi, @cliang 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with Excel. 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.