Ranking Week Number per Month
I have gotten a little stuck using the Rank Function and was wondering if someone would help me out.
I have a week dimensioned model.
I am trying to rank the dimensioned weeks to produce a resulting module that provides the week number within a calendar month. I would like the rank to reset every month
Week 1 of FY21 is the Week starting 03/10/2021 this would have a rank of 1
Week 6 of FY21 is the Week starting 07/11/2021 this would have a rank of 2
I have been able to dimension the module together, however when I use the rank function I just get the number 1 repeating.
For reference my Rank line item is using the formula: RANK(Starting week of dimension)
I would appreciate any and all help 🙂
Have a lovely day