Same period last year with offset

[awitt]

I've searched for this answer a few different ways and I cannot come up with a good solution. 

I am doing YOY sales comparrisons and using the SAMEPERIODLASTYEAR function. I have a date table as well as an orders table with multiple years of sales data. 

I need daily comparrisons to be accurate not for the calendar date, but for the week and day of that week that match. So this Monday's sales need to be compared against last years Monday sales for this week. Today is 4/15/2019 and the equivalent date for last year would be 4/16/2018. 

This is my date table.

Date =

ADDCOLUMNS (

CALENDAR (DATE(2016,1,1), DATE(2020,12,31)),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"DayOfYear", DateDiff(date(year([Date]),1,1),[Date],DAY)+1,

"Weeknumber", weeknum([Date],1),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"FutureDate", If([Date]>TODAY(),"Future","Past"),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"WeekAndDayNumber", WEEKNUM([Date],1)&WEEKDAY([Date]),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

[v-cherch-msft · Accepted solution]

Hi @awitt 

You may try below measure:

Measure = 
CALCULATE (
    SUM ( Table1[Sales] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Year]
            = MAX ( 'Date'[Year] ) - 1
            && 'Date'[Weeknumber] = MAX ( 'Date'[Weeknumber] )
            && 'Date'[DayOfWeekNumber] = MAX ( 'Date'[DayOfWeekNumber] )
    )
)

Regards,