Forum Discussion
Solved
Fiscal Week
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 number...
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 AddDatesBasedOnStartDateAndDayIndexIf 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.
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"