Forum Discussion

lokosrio's avatar
lokosrio
Icon for Helper II rankHelper II
4 years ago
Solved

RANKX function for fiscal year-week with sorting by date

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 y...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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]

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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]