Forum Discussion

JulianTobon's avatar
JulianTobon
Helper I
9 years ago

Function To Calculate Fiscal Week

Hi all,   I want to calculate the number of weeks based on the date of sale, I used the formula:  week = WEEKNUM(Scorecard[SalesDate],2) This works very well but not for what I need, Due to work ...
Need Help
Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

JulianTobon

 

Ok, maybe your structure is different in my scenario. 

 

Hi, 

Sean's avatar
Sean
Community Champion
9 years ago

VvelardeWhere's week 28?

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    Sean

     

    Yes, the formula need some adjustment.

     

    WeeKFY2 =
VAR WeekStartinFY =
    WEEKNUM ( DATE ( Calendario[FiscalYear], 7, 1 ); 2 )
RETURN
    IF (
        Calendario[WeeK] < WeekStartinFY,
        WeekStartinFY + Calendario[WeeK],
        Calendario[WeeK] - WeekStartinFY
            + 1
    )

     

    Now Appears.But don't know if this the solution to Julian's Question.

     

     

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    Yes no need to add +1 when adding the 2 numbers

    JulianTobondoes this work better?

     

     

  • JulianTobon's avatar
    JulianTobon
    Helper I
    9 years ago

    We are on the right track, but as you can see an error is generated at the calendar year change. For example is January 1 should be week 27 and the formula detects it as 28 and changes the next day to 29.

     

  • JulianTobon's avatar
    JulianTobon
    Helper I
    9 years ago

    Sean No :smileysad:

  • gimpexe's avatar
    gimpexe
    Frequent Visitor
    8 years ago

    any development on this? ive got the same issue.

     

    trying to create a column for Fiscal Week 1-53, starting on July 1st.