RANKX function for fiscal year-week with sorting by date

[lokosrio]

Hi,

I want to create an RANKX function with grouping by FYYear-Week, but also sort by date, right now I have a function:

 RANKX ( dim_calendar, dim_calendar[FYYearWeek],, ASC, DENSE )

but as you can see on the picture below it is not sorted properly, because FYYearWeek = 2022/52 is bigger than FYYearWeek = 2022/01:

Logically it makes sense, but I want to add sorting by date to my formula, so instead of index = 107 should be 55 and so on

Best regards

[amitchandak · Accepted solution]

@lokosrio , for that case we create an FY year week column on the year of week start date.

FY Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[FY Year]=EARLIER([FY Year])),'Date'[Start of Week]),[Date],DAY),7)+1

Then create = [FY year]*100 + [FY Week]