Forum Discussion

Dunner2020's avatar
Dunner2020
Post 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:

RY Week = QUOTIENT(DATEDIFF(MINX(FILTER(dates,Dates[Regulatory Year]=EARLIER(Dates[Regulatory Year])),Dates[Week Starting]),Dates[Date],DAY),7)+1
 
However, I noticed that my RY week number values go up to 90 :). I have attached the file for your review. Could you please point me out where I made the mistake?
 
Need Help
Show and Tell
Tips and Tricks

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Dunner2020 , I checked it. It because of FY. I have used Endofyear for FY end date. And Using today as the end date. As Endofyear do not go beyond the calendar date. So for last year, FY end date is incorrect and it was creating the wrong end date.

     

    I correct calendar like

    Date = CALENDAR(date(2013,04,01),date(2021,03,01))

     

    OR you can create an FY like, Based your need

    If(month([Date])<=3, year([Date]) -1, Year([Date]))  // This can any month

    • Dunner2020's avatar
      Dunner2020
      Post 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