Forum Discussion

Dunner2020's avatar
Dunner2020
Icon for Post Prodigy rankPost Prodigy
5 years ago

RY Week number

Hi amitchandak ,   I am following your youtube tutorial (https://www.youtube.com/watch?v=q_Ke80KNcEw) to create the RY week no. I noticed that  The formula I have written for FY week is as follow:...
Need Help
Show and Tell
Tips and Tricks
Dunner2020's avatar
Dunner2020
Icon for Post Prodigy rankPost Prodigy
5 years ago

amitchandak , Thanks for the clarification. I generated the RY Year the way you mentioned in your reply and it fixed the problem.However, I noticed that Week Rank is producing value upto 367. The formula is used for Week Rank is 

Week Rank = RANKX(ALL(Dates),Dates[Week Starting],,ASC,Dense)
 
and the Week Starting is calculated using the following formula:
Week Starting = Dates[Date]-Dates[Day In Week]
 
I did not add 1 in Week starting as in my data Day in Week starts from 0 (i.e. Monday). Could you please advise how to fix that issue?
 
 
 
 
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Dunner2020,

It should work as a general weeknum function to return the correct week number if you are working with a common calendar table and filtered based on the year field. 

 

RW =
RANKX (
    FILTER ( 'Table', YEAR ( [Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) ) ),
    [Week start],
    ,
    ASC,
    DENSE
)

 

Since your table needs to calculated based on the fiscal fields. If RYear field does not work well with the above expression, I think you may need to add offset to modify these fiscal fields to make them work as common calendar fields.

Regards,

Xiaoxin Sheng