Comparison of data between two years by week number

[Pearl456]

Hello, 

Attempting to create a matrix to compare this year's sales to 2020 sales by week number. 

Current code for last year is;

Sales SPLY = CALCULATE(sum('Sales Detail'[ABC Revenue]),SAMEPERIODLASTYEAR('Date'[Date]))

2021 sales data is reflecting correctly by week number, but 2020 is not pulling data from 2020 week number. 
Example: 2020 data is pulling dates 1/1/20 - 1/2/20, rather than 1/1/20 - 1/4/20. 

Week #	2021 Sales	2020 Sales
1	2,520	2,556
2	7,708	9,764
3	7,697	7,802

Any guidance how to achieve last years data by the correct week number would be very appreciated. 

[v-eqin-msft · Accepted solution]

Hi @Pearl456 ,

I have built a data sample :

Week Table = 
DISTINCT (
    SELECTCOLUMNS (
        CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2021, 12, 31 ) ),
        "Week", WEEKNUM ( [Date], 2 )
    )
)

You could create measures to calculate the sum of each week:

#2020 Sales =
CALCULATE (
    SUM ( 'Sales Detail'[ABC Revenue] ),
    FILTER (
        'Sales Detail',
        YEAR ( 'Sales Detail'[Date] ) = 2020
            && WEEKNUM ( 'Sales Detail'[Date], 2 ) = MAX ( 'Week Table'[Week] )
    )
)

#2021 Sales =
CALCULATE (
    SUM ( 'Sales Detail'[ABC Revenue] ),
    FILTER (
        'Sales Detail',
        YEAR ( 'Sales Detail'[Date] ) = 2021
            && WEEKNUM ( 'Sales Detail'[Date], 2 ) = MAX ( 'Week Table'[Week] )
    )
)

Here is the final output:

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

[TomMartens]

Hey @Pearl456 ,

can you please provide a screenshot that shows all the days that are in the first week of 2021 and another screenshot that are in the first week of 2020.

This article explains how the function SAMEPERIODLASTYEAR works internally: https://dax.guide/sameperiodlastyear/

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom

[Pearl456]

Hello @TomMartens ,

I don't have an additional screen shot to share. The sales difference was discovered when validating the BI data to our accounting system by journal dates. BI 2020 sales amount was only from 1/1/2020 through 1/2/2020, rather than the actual first week of 2020 which are dates 1/1/2020 through 1/4/2020. Our third party company has restricted access to the data so I am unable to add a column within the data for only 2020 sales. Is there a DAX function to hard code 2020 sales data so I can get the goal of weekly comparisons between 2020 & 2021? 

Thank you. 

[TomMartens]

Hey @Pearl456 ,

without further information about your data model, I'm not able to provide further guidance.

Regards,

Tom