Forum Discussion

StephenGW's avatar
StephenGW
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 earlie...
Need Help
  • Icey's avatar
    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.

Anonymous's avatar
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
            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"