Fiscal Week

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-5-Power-BI/ba-p/1187502

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

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

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"