cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper II

## 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 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}})),
#"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}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"WeekInFM", Int64.Type}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded WeekDay",{{"WeekDay", Int64.Type}}),
RemoveUnneededColumns = Table.RemoveColumns(#"Changed Type2",{"Weeks", "NumWeeks", "WeekDay"}),
in

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

Thanks,

SG

1 ACCEPTED SOLUTION
Community Support

Hi @StephenGW ,

``````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}})),
#"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}}),
#"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]}}),
#"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"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded WeekDay",{{"WeekDay", Int64.Type}}),
RemoveUnneededColumns = Table.RemoveColumns(#"Changed Type2",{"Weeks", "NumWeeks", "WeekDay"}),
in

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.

7 REPLIES 7
Frequent Visitor

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,
if (FiscalMonthBaseIndex >= 12 or FiscalMonthBaseIndex < 0) then
0
else
FiscalMonthBaseIndex,
),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"FiscalBaseDate", type date}}),
#"Changed Type1", "Fiscal Year", each Date.Year([FiscalBaseDate]), Int64.Type
),
#"Inserted Year1",
"Fiscal Quarter",
each "FY" & Text.End(Text.From([Fiscal Year]), 2) & " Q" & Text.From(Date.QuarterOfYear([FiscalBaseDate]))
),
#"Inserted Quarter1",
"Fiscal Month",
each
"FY"
& Text.End(Text.From([Fiscal Year]), 2)
& " m"
),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Month1", {"FiscalBaseDate"}),
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,
in
Text.End(Text.From(weekYear), 2) & " W" & week_dateString,
Table.AddColumn(Custom1, "Year-Week", each getISO8601Week([Date])), {{"Year-Week", type text}}
),
"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

Community Support

Hi @StephenGW ,

``````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}})),
#"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}}),
#"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]}}),
#"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"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded WeekDay",{{"WeekDay", Int64.Type}}),
RemoveUnneededColumns = Table.RemoveColumns(#"Changed Type2",{"Weeks", "NumWeeks", "WeekDay"}),
in

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.

Helper II

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

Helper II

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

Helper II

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

Super User

@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

Super User

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