Calculating Comparable Year Sales with 4-4-5 Calendar

[azimuthbrett]

I'm struggling to work out how to build a chart table that shows current year ("CY") sales by the 'Day of Year', along with prior year ("PY") comparable data for sales one year ago. The dataset uses a 4-4-5 calendar, so every year begins on a Monday. For instance, FY2019 begins on 12/31/2018, while FY2018 begins on 1/1/2018.

The desired outcome is a table with columns showing:

• The current year date in 'MM/DD' format (with 20 days trailing from the current report date)
• The weekday (i.e. 'Monday', 'Tuesday'...)
• The CY sales for each of the days in the 20-day lookback period
• Comparable PY sales for the same/equivalent day in the previous year.

There is a complicating factor, due to the 4-4-5 calendar. Because 4-4-5 calendars use 364 days in a year, every few years you end up with one extra week (like a 'leap week'), which is needed to catch up. 2017 had a 'leap week', with 371 days in the 4-4-5 year. 2016 and 2018 have 364 days.

This extra week makes it challenging to just compare CY and PY based on the day of the year value...For instance, in the 20 day lookback (assuming a report date as of the first week of January 2019), the last day of 2018 is numbered 364. But comparing that to 2017, the last day is numbered 371.  

We added flags to the calendar table to identify CY and PY days that fall within the 20-day lookback. The following formulae work for calculating CY and PY values, but the chart table then fails to line up the comparable days. 

SUM( { 1< WeekBased445.CY_20_Flag = {1} > } Sales)  //CY Trailing 20 days
SUM( { 1< WeekBased445.PY_20_Flag = {1} > } Sales)  //PY Trailing 20 days

The issue is clearly that the 'MM/DD' dimension column we're using includes the associated year and date...so the calculations are not lining up the way we want them to.

The pseudocode for what we want with the PY sales data column is:

// Calculate PY data in each row based on CY date, which will be 364 days previous
SUM(
  { 1< Date = { Date in Dimension Column - 364 } > }
  Sales
)

 I've tried doing this with variations on AGGR, and just about everything else I can think of. Basically I want to show the comparable sales data for the day that is exactly 364 days previous to the CY sales data, and I want it in the same row. I'm sure there is something simple that I'm missing, but it's been a year or more since I've fooled around with complex aggregations and this is kicking my butt. Appreciate any guidance or advice.

Sample data is attached.