Fiscal Week Reset

[StephenGW]

All,

I found this code for the advanced editor on here somewhere but I struggle to pick it apart to the level I need.

let
// Enter the date in Advanced Editor for the first day of the earliest Fiscal Year in #date(yyyy,m,d) format
StartDate = #date(2021, 1, 3),
// Enter the desired range of years as List in Advanced Editor
YearRange = {2021..2050},
// In Advanced Editor, use one of the two options for this step (code out the one not used with //) to either hard code or dynamically calculate the years with 53 weeks. Substitute a different evaluation criterion as needed.
YearsWith53Weeks = {2020},
//YearsWith53Weeks = List.Select(YearRange, each Date.DayOfWeekName(#date(_,11,30)) = "Saturday"),
StartingTable = Table.FromColumns({YearRange}, {"Year"}),
AddNumberOfWeeksColumn = Table.AddColumn(StartingTable, "Weeks", each if List.Contains(YearsWith53Weeks, [Year]) then 53 else 52),
#"Changed Type4" = Table.TransformColumnTypes(AddNumberOfWeeksColumn,{{"Year", Int64.Type}, {"Weeks", Int64.Type}}),
// In the Advanced Editor, enter the two patterns for 52 and 53 week years as a list of weeks per fiscal month
AddListOfMonthAndWeekCounts = Table.AddColumn(#"Changed Type4", "Custom", each if [Weeks] = 53 then List.Zip({{1..12}, {4,4,5,4,4,5,4,4,5,4,5,5}}) else List.Zip({{1..12}, {4,5,4,4,5,4,4,5,4,4,5,4}})),
#"Expanded Custom" = Table.ExpandListColumn(AddListOfMonthAndWeekCounts, "Custom"),
#"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"FM", "NumWeeks"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"FM", Int64.Type}, {"NumWeeks", Int64.Type}}),
AddListOfWeeksColumn = Table.AddColumn(#"Changed Type", "WeekInFM", each {1..[NumWeeks]}),
#"Expanded Custom1" = Table.ExpandListColumn(AddListOfWeeksColumn, "WeekInFM"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"WeekInFM", Int64.Type}}),
AddWeekIndex = Table.AddIndexColumn(#"Changed Type1", "FW_Index", 1, 1, Int64.Type),
Add7DayListPerWeek = Table.AddColumn(AddWeekIndex, "WeekDay", each {1..7}),
#"Expanded WeekDay" = Table.ExpandListColumn(Add7DayListPerWeek, "WeekDay"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded WeekDay",{{"WeekDay", Int64.Type}}),
RemoveUnneededColumns = Table.RemoveColumns(#"Changed Type2",{"Weeks", "NumWeeks", "WeekDay"}),
AddDayIndex = Table.AddIndexColumn(RemoveUnneededColumns, "DayIndex", 0, 1, Int64.Type),
AddDatesBasedOnStartDateAndDayIndex = Table.AddColumn(AddDayIndex, "Date", each Date.AddDays(StartDate, [DayIndex]), type date)
in
AddDatesBasedOnStartDateAndDayIndex

This does everything I need it to other than the FW_Index, I need this to reset when my fiscal year starts over. Currrently it just continues to count up. Does anybody know how I can adjust this or even add a column that will start the weeks over when the new fiscal year starts?

Thanks

[TiagoLugatto]

I use this code, the Fiscal Week solution may be different from the one you use, but it's interesting to compare

let

    // configurations start

    Today = Date.From(DateTime.LocalNow()),

    // today's date

    FromYear = 2013,

    // set the start year of the date dimension. dates start from 1st of January of this year

    ToYear = 2033,

    // set the end year of the date dimension. dates end at 31st of December of this year

    StartofFiscalYear = 4,

    // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7

    firstDayofWeek = Day.Monday,

    // set the week's start day, values: Day.Monday, Day.Sunday....

    // configuration end

    FromDate = #date(FromYear, 1, 1),

    ToDate = #date(ToYear, 12, 31),

    Source = List.Dates(FromDate, Duration.Days(ToDate - FromDate) + 1, #duration(1, 0, 0, 0)),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),

    FiscalMonthBaseIndex = 13 - StartofFiscalYear,

    adjustedFiscalMonthBaseIndex =

        if (FiscalMonthBaseIndex >= 12 or FiscalMonthBaseIndex < 0) then

            0

        else

            FiscalMonthBaseIndex,

    #"Added Custom" = Table.AddColumn(

        #"Changed Type", "FiscalBaseDate", each Date.AddMonths([Date], adjustedFiscalMonthBaseIndex)

    ),

    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"FiscalBaseDate", type date}}),

    #"Inserted Year1" = Table.AddColumn(

        #"Changed Type1", "Fiscal Year", each Date.Year([FiscalBaseDate]), Int64.Type

    ),

    #"Inserted Quarter1" = Table.AddColumn(

        #"Inserted Year1",

        "Fiscal Quarter",

        each "FY" & Text.End(Text.From([Fiscal Year]), 2) & " Q" & Text.From(Date.QuarterOfYear([FiscalBaseDate]))

    ),

    #"Inserted Month1" = Table.AddColumn(

        #"Inserted Quarter1",

        "Fiscal Month",

        each

            "FY"

                & Text.End(Text.From([Fiscal Year]), 2)

                & " m"

                & (Text.PadStart(Text.From(Date.Month([FiscalBaseDate])), 2, "0"))

    ),

    #"Removed Columns" = Table.RemoveColumns(#"Inserted Month1", {"FiscalBaseDate"}),

    #"Added Custom4" = Table.AddColumn(#"Removed Columns", "Year-Month", each Date.ToText([Date], "yy-MM")),

    getISO8601Week = (someDate as date) =>

        let

            getDayOfWeek = (d as date) => let result = 1 + Date.DayOfWeek(d, Day.Monday) in result,

            getNaiveWeek = (inDate as date) =>

                let

                    // monday = 1, sunday = 7

                    weekday = getDayOfWeek(inDate),

                    weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),

                    ordinal = Date.DayOfYear(inDate),

                    naiveWeek = Number.RoundDown((ordinal - weekday + 10) / 7)

                in

                    naiveWeek,

            thisYear = Date.Year(someDate),

            priorYear = thisYear - 1,

            nwn = getNaiveWeek(someDate),

            lastWeekOfPriorYear = getNaiveWeek(#date(priorYear, 12, 28)),

            lastWeekOfThisYear = getNaiveWeek(#date(thisYear, 12, 28)),

            weekYear = if nwn < 1 then priorYear else if nwn > lastWeekOfThisYear then thisYear + 1 else thisYear,

            weekNumber = if nwn < 1 then lastWeekOfPriorYear else if nwn > lastWeekOfThisYear then 1 else nwn,

            week_dateString = Text.PadStart(Text.From(Number.RoundDown(weekNumber)), 2, "0")

        in

            Text.End(Text.From(weekYear), 2) & " W" & week_dateString,

    Custom1 = Table.AddColumn(#"Added Custom4", "Day Name", each Date.DayOfWeekName([Date]), type text),

    #"Added custom1" = Table.TransformColumnTypes(

        Table.AddColumn(Custom1, "Year-Week", each getISO8601Week([Date])), {{"Year-Week", type text}}

    ),

    #"Added Custom1" = Table.AddColumn(

        #"Added custom1",

        "Fiscal Week",

        each

            let

                fiscalStartWeek = Number.FromText(Text.End(getISO8601Week(#date(Date.Year([Date]), 4, 1)), 2)),

                week = Number.FromText(Text.End([#"Year-Week"], 2)),

                lastWeekOfFiscalYear = Number.FromText(

                    Text.End(getISO8601Week(#date(Date.Year([Date]) - 1, 12, 28)), 2)

                ),

                lastfiscalStartWeek = Number.FromText(Text.End(getISO8601Week(#date(Date.Year([Date]) - 1, 4, 1)), 2)),

                fiWeek =

                    if [Date] < #date(Date.Year([Date]), 4, 1) then

                        if week < fiscalStartWeek then

                            lastWeekOfFiscalYear + week - lastfiscalStartWeek + 1

                        else if week = fiscalStartWeek then

                            if [Date] < #date(Date.Year([Date]), 4, 1)then

                                lastWeekOfFiscalYear + week - lastfiscalStartWeek + 1

                            else

                                week - fiscalStartWeek + 1

                        else

                            week - fiscalStartWeek

                    else

                        week - fiscalStartWeek + 1

            in

                "FY" & Text.End(Text.From([#"Fiscal Year"]), 2) & " w" & Text.End(Text.From(fiWeek), 2)

    )

in

    #"Added Custom1"

[Icey · Accepted solution]

Hi @StephenGW ,

Please check if this could meet your requirements:

FY = 
IF (
    [Date] >= DATE ( [Year], 1, 3 )
        && [Date]
            <= DATE ( [Year] + 1, 1, 2 ),
    "FY" & RIGHT ( [Year], 2 ),
    "FY"
        & RIGHT ( [Year] - 1, 2 )
)

FW = 
RANKX (
    FILTER ( FiscalWeek, FiscalWeek[FY] = EARLIER ( FiscalWeek[FY] ) ),
    [FW_Index],
    ,
    ASC,
    DENSE
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@StephenGW , In DAX if you want

Week No = var _1= QUOTIENT(DATEDIFF(STARTOFYEAR('Date'[Date], "1/31"),'Date'[Date],DAY),7)+1 return _1

or

Week No =
Var _st = if(month([Date]) <2 , date(year([Date])-1,2,1) , date(year([Date]),2,1) )
var _1= QUOTIENT(DATEDIFF(_st,'Date'[Date],DAY),7)+1
return _1

[StephenGW]

@amitchandak 

I tried both of these and they are not working how I need them to. Here are some screenshots to see if you can see what might be wrong.

Start:

Starting over:

Second Dax:

Maybe it's because of the start of my table? It starts at 2021 which started on 1/3/21 and ends on 1/2/22 so that is all included in my 2021 fiscal year. Any ideas?

[amitchandak]

@StephenGW , Power Query

Not tested

Number.IntegerDivide(Duration.Days([Date]-(if Date.Month([Date]) <2 then #date(Date.Year([Date])-1,2,1) else #date(Date.Year([Date]),2,1) )),7)+1