Forum Discussion

Helper II

4 years ago

Solved

Fiscal Week Reset

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

Need Help

Icey

4 years ago

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.

Fiscal Week Reset.pbix186 KB

5 Replies

Icey

Community Support

4 years ago

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.

Fiscal Week Reset.pbix186 KB

amitchandak
Super User
4 years ago
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
- amitchandak
  Super User
  4 years ago
  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
- StephenGW
  Helper II
  4 years ago
  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?

Anonymous

Not applicable

3 years ago

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

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)

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

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

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

)

#"Added Custom1"

Forum Discussion

Fiscal Week Reset

5 Replies

Related content

Fiscal Week

Cumulative Fiscal week DAX Calc

Fiscal Week

How to reset Eventhouse Ingestion from Batching to Streaming ?

Creating a fiscal week column