Function To Calculate Fiscal Week

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.

HI Vvelarde 

This formula Did not give correct data

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?

JulianTobon

Ok, maybe your structure is different in my scenario. 

Hi, 

VvelardeWhere's week 28?

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

JulianTobondoes this work better?

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.

Sean No :smileysad:

any development on this? ive got the same issue.

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

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
    )

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)

JulianTobon Hi ,

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

thanks