Issue with Calculating Cumulative Sales for Same Weekday Last Year

Hello Power BI Community,

I am encountering an issue with calculating the cumulative sales for the same weekday last year. Here are the key details of the problem:

1. Same Weekday Last Year:

   • I am not looking to calculate cumulative sales for the same date last year but for the same weekday last year.
     (ex. this year:2024.May.1st(Sat) -> Same Weekday Last Year : 2023.May.3rd(Sat))

2. Data Structure:

   • The column for the same weekday last year is not in the calendar table but is in a separate table. I have established a relationship between this table and the calendar table.
     

      

3. Objective:

   • I want to calculate the cumulative sales of "RegisterPrice" from the Purchase_Data table for the same weekday last year using a DAX measure.

4. Current Issue:

   • The current measure I have returns all blank values instead of the cumulative sales for the same weekday last year.

Current Measure:

Here is the current DAX measure that I am using:

Cumulative Sales for Same Weekday Last Year (in Millions) = 
VAR MaxCurrentDate = MAX('Calendar_Table'[Date])
VAR PreviousDate = CALCULATE(
    MAX('Previous_Date_Table'[SameWeekdayLastYearDate]),
    'Previous_Date_Table'[SameWeekdayLastYearDate] <= MaxCurrentDate
)
RETURN
CALCULATE(
    SUMX(
        FILTER(
            ALL('Previous_Date_Table'),
            'Previous_Date_Table'[SameWeekdayLastYearDate] <= PreviousDate
        ),
        CALCULATE(
            SUM('Purchase_Data'[RegisterPrice]),
            TREATAS(
                VALUES('Previous_Date_Table'[SameWeekdayLastYearDate]),
                'Calendar_Table'[Date]
            )
        )
    )
) / 1000000

Explanation:

1. MaxCurrentDate:

   • Retrieves the maximum current date from the calendar table.

2. PreviousDate:

   • Retrieves the corresponding same weekday last year date up to the current date.

3. SUMX and FILTER:

   • Calculates cumulative sales for the same weekday last year.

Despite this, the measure returns blank values.

Could you please help me identify what I might be doing wrong and how to correct this measure?

Thank you in advance for your assistance.