Ranked Column based on "Start Date" per User/Quarter.

[StuartSmith]

I have a table similar to the below where it counts the number of "Half" days a user has within each quarter.  So "Jane Doe" had 2 "Half" days in "Q2 2023" and "John Smith" had 3 "Half" days in "Q3 2023".  

I am now trying to create a new column that will RANK "Half" days per user & quarter based on "Start Date". Hope that makes sense.

Thanks in advance.

[ryan_mayu · Accepted solution]

@StuartSmith 

pls try this

rank =

var _start=minx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[quarter year]=EARLIER('Table'[quarter year])&&'Table'[Duration]="Half"),'Table'[Start Date])

return if('Table'[Duration]<>"Half",0,countx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[quarter year]=EARLIER('Table'[quarter year])&&'Table'[Start Date]>=_start&&'Table'[Start Date]<=EARLIER('Table'[Start Date])&&'Table'[Duration]="Half"),'Table'[Name]))

pls see the attachment below

[StuartSmith]

Thanks Ryan, that looks like it done the trick and worked. Just need to test throughly, but thanks.

[ryan_mayu]

you are welcome