I have an interesting dilemma. In Power BI I would like to make comparisons to the same day of week same week number last year. In Power BI I have not found a way to create this. There is the function SAMEPERIODLASTYEAR() however, this is relative to the date so if the date is 1/1/2024 it will look at 1/1/2023. I need one instead that will look at our retail year. The structure of our Retail Year is such that the week starts on Sunday so if 12/31/2023 is on a Sunday it is part of week 1 of the year 2024. I have been messing with functions to help create this such as:

WeekNumber = 
VAR CurrentYear = YEAR('Calendar'[Date])
VAR CountWeek53 =
    CALCULATE(
        COUNTROWS('Calendar'),
        FILTER(
            'Calendar',
            YEAR('Calendar'[Date]) = CurrentYear &&
            WEEKNUM('Calendar'[Date], 1) = 53
        )
    )
VAR CountWeek54 =
    CALCULATE(
        COUNTROWS('Calendar'),
        FILTER(
            'Calendar',
            YEAR('Calendar'[Date]) = CurrentYear &&
            WEEKNUM('Calendar'[Date], 1) = 54
        )
    )
RETURN
IF(
    WEEKNUM('Calendar'[Date], 1) = 54 && CountWeek54 >= 7,
    54,
    IF(
        WEEKNUM('Calendar'[Date], 1) = 53 && CountWeek53 < 7,
        1,
        WEEKNUM('Calendar'[Date], 1)
    )
)

I formatted this code because every so often there can during a leap year be 54 weeks. I thus created a Week Number that will look at the date and if it is part of week number 53 or 54 it will look at how many days are in that week, if it is less than 7 it will add it to week 1 of the new year. I also created the retail year column this way:

Fiscal Year = IF(MONTH('Calendar'[Date]) == 12 && 'Calendar'[WeekNumber] == 1, INT(YEAR('Calendar'[Date]) + 1), INT(YEAR('Calendar'[Date])))

However, when it comes to actually building a working column or measure for the Sales, GM, or any other measure LY it does not work.

Due to the nature of retail some items available last year are not available this year and vice versa. Thus, you cannot make a column like so:

PreviousYearSalesMeasure = 
VAR CurrentDate = MAX('Calendar'[Date])
VAR CurrentYear = YEAR(CurrentDate)
VAR CurrentDayOfWeek = WEEKDAY(CurrentDate)
VAR CurrentWeekNumber = WEEKNUM(CurrentDate, 1)

-- Find the date from the previous year with the same week number and day of the week
VAR PreviousYearDate = 
    CALCULATE(
        MAX('Calendar'[Date]),
        FILTER(
            ALL('Calendar'),
            YEAR('Calendar'[Date]) = CurrentYear - 1 &&
            WEEKDAY('Calendar'[Date]) = CurrentDayOfWeek &&
            WEEKNUM('Calendar'[Date], 1) = CurrentWeekNumber
        )
    )

-- Fetch the sales value for the previous year date and specified group
VAR PreviousYearSalesValue = 
    CALCULATE(
        SUM('Sales'[Net Sales]),
        FILTER(
            ALL('Sales'),
            'Sales'[Date] = PreviousYearDate &&
            'Sales'[Store Number] = MAX('Sales'[Store Number]) &&
            'Sales'[Current Department Code] = MAX('Sales'[Current Department Code]) &&
            'Sales'[Current Class Code] = MAX('Sales'[Current Class Code]) &&
            'Sales'[Current Fineline Code] = MAX('Sales'[Current Fineline Code]) &&
            'Sales'[Current Vendor Code] = MAX('Sales'[Current Vendor Code])
        )
    )

RETURN
    IF(
        ISBLANK(PreviousYearDate),
        BLANK(),  -- Return BLANK if no corresponding date exists
        PreviousYearSalesValue
    )

In other words, I need a way to include sales that are attributed to discontinued items or items that are not sold the same day of week and week number without creating additional rows. I know this is a tall ask but, what would you recommend?