Fiscal Week

[StephenGW]

Hello,

I have a table that gets me the fiscal month and year but for the fiscal week number it just has an index number that continues to grow instead of restarting every year. I get these numbers using this in the advanced editor which I found on here I think but I cannot find it again.

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

Does anybody have an easy way to get the fiscal week number starting over each year?

Thanks,

SG

[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"

[v-kkf-msft · Accepted solution]

Hi @StephenGW ,

Please try the following code:

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}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Year"}, {{"All", each _, type table [Year=nullable number, Weeks=nullable number, FM=nullable number, NumWeeks=nullable number, WeekInFM=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All], "New FW Index", 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Year", "All"}),
    #"Expanded Custom2" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Year", "Weeks", "FM", "NumWeeks", "WeekInFM", "New FW Index"}, {"Year", "Weeks", "FM", "NumWeeks", "WeekInFM", "New FW Index"}),
AddWeekIndex = Table.AddIndexColumn(#"Expanded Custom2", "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

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StephenGW]

@v-kkf-msft 

That worked perfectly as far out as I can see on my calendar! Thank you very much for the great response!

[StephenGW]

Anybody have any ideas on how I can fix this? I have a report that I need to get out but the fiscal weeks are just slightly off.

Thanks

[StephenGW]

@amitchandak 

I could not figure out how to make your DAX work how I needed. I tried adding a column to my existing table using this DAX,

Week No = var _1=  QUOTIENT(DATEDIFF(STARTOFYEAR('Date'[Date]),'Date'[Date],DAY),7)+1 return if(_1<52,_1,52)

But that starts over each calendar year from what I could tell and my fiscal calendar does not start at the beginning of the calendar year. For example this year started on 1/2/22 instead of 1/1 and some years will also have 53 weeks. I think were close but it's just off a bit. Any ideas on what I need to modify? Would there be an easy DAX that looks at my fiscal month and adds one everytime it changes and resets when it hits 1 again?

Thanks

[amitchandak]

@StephenGW , try like

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

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

[amitchandak]

@StephenGW , I have DAx for that start every year, see if that can help

Week That Resets Yearly
https://community.powerbi.com/t5/Community-Blog/Week-That-Resets-Yearly-Decoding-Date-and-Calendar-3...