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
JulianTobon's avatar
JulianTobon
Helper I
9 years ago

HI Vvelarde 

 

This formula Did not give correct data

 

JulianTobon's avatar
JulianTobon
Helper I
9 years ago

Phil_Seamark excuseme, Your form is a little more successful, what I can detect in it is that it starts the count of the week on Sunday, but it should start on Monday. We have to indicate that in the Formula?

  • freshwave's avatar
    freshwave
    Helper I
    8 years ago

     

    For me this worked perfectly.  For crossing over the Calendar Year you need to subtract one.

    WeeKFY2 =
VAR WeekStartinFY =
    WEEKNUM ( DATE ( Calendario[FiscalYear], 7, 1 ), 2 )
RETURN
    IF (
        Calendario[WeeK] < WeekStartinFY,
        WeekStartinFY + Calendario[WeeK]
            - 1,
        Calendario[WeeK] - WeekStartinFY
            + 1
    )
  • freshwave's avatar
    freshwave
    Helper I
    8 years ago

    I was able to get the same results in Power Query with the following code:

     

    InsertFiscalWeek = Table.AddColumn(PreviousItem, "FiscalWeek", each if [Week] < Date.WeekOfYear(#date([FiscalYear], 7, 1))
    then [Week] + Date.WeekOfYear(#date([FiscalYear], 7, 1)) - 1
    else [Week] - Date.WeekOfYear(#date([FiscalYear], 7, 1)) + 1, Int64.Type)

  • Hanuma's avatar
    Hanuma
    Regular Visitor
    7 years ago

    JulianTobon Hi ,

     Please share the solution if you have the solution for the fiscal week starts with monday

    thanks