Issue with Calculating Cumulative Sales for Same Weekday Last Year

[RYOH_0130]

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.

[v-tianyich-msft]

Hi @RYOH_0130 ,

Not sure about your date table, please provide sample data that maps your data model

Best regards,
Community Support Team_ Scott Chang

[RYOH_0130]

Dear @v-tianyich-msft 

Thank you for your response.
Please find below the sample data for the data map for your review.

Best regads,
Ryo

Previous Data Table
Date	Day	SameWeekday Last Year's Date	Last Year's Day
3-Jan-20	Fri	4-Jan-19	Fri
10-Jan-20	Fri	11-Jan-19	Fri
17-Jan-20	Fri	18-Jan-19	Fri
24-Jan-20	Fri	25-Jan-19	Fri
31-Jan-20	Fri	1-Feb-19	Fri
1-Jan-21	Fri	3-Jan-20	Fri
8-Jan-21	Fri	10-Jan-20	Fri
Calender Table
Date,	Year_yyyy,	Month_Value,	Day_Value,
1-May-17	2017	5	1
2-May-17	2017	5	2
3-May-17	2017	5	3
4-May-17	2017	5	4
5-May-17	2017	5	5
6-May-17	2017	5	6
7-May-17	2017	5	7
GGCA_Purchase_data
ReceptionDate	RegisterPrice	RegisterPriceTaxIn
1-May-17	1000	1100
2-May-17	1500	1650
3-May-17	2000	2200
4-May-17	1000	1100
5-May-17	1200	1320

[v-tianyich-msft · Accepted solution]

Hi @RYOH_0130 ,

I did simple samples, mainly for the same weekday screenings last year for the study and you can check the results below:

LastYearSameWeekDay = var _t = ADDCOLUMNS('Table',"a",SUMX(FILTER(ALL('Table'),YEAR([Date])=YEAR(EARLIER([Date]))-1&&MONTH([Date])=MONTH(EARLIER([Date]))&&DAY([Date])>=DAY(EARLIER([Date]))-7&&WEEKDAY([Date])=WEEKDAY(EARLIER([Date]))),[Value]))
RETURN SUMX(_t,[a])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.