topic Re: Issue with Calculating Cumulative Sales for Same Weekday Last Year in DAX Commands and Tips

Issue with Calculating Cumulative Sales for Same Weekday Last Year

RYOH_0130 — Mon, 22 Jul 2024 03:48:42 GMT

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:

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))
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.
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.
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:

MaxCurrentDate:
- Retrieves the maximum current date from the calendar table.
PreviousDate:
- Retrieves the corresponding same weekday last year date up to the current date.
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.

Re: Issue with Calculating Cumulative Sales for Same Weekday Last Year

Anonymous — Tue, 23 Jul 2024 03:02:39 GMT

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

Re: Issue with Calculating Cumulative Sales for Same Weekday Last Year

RYOH_0130 — Wed, 24 Jul 2024 10:14:16 GMT

Dear Anonymous

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

Re: Issue with Calculating Cumulative Sales for Same Weekday Last Year

Anonymous — Wed, 31 Jul 2024 06:25:59 GMT

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.