Calculating Year Ago Data With Inconsistent UPC Column

[ESemba22]

Hello! I feel like this is a basic question that has a simple fix but I have toiled trying to figure it out and can't come up with the answer.  I have weekly Amazon sales data with columns for ASINS (UPCs), week ending date and $ sales. I want to be able to visualize in line charts or tables what my sales over the last year were vs prior year.  The way I tried to do this is by making a date table that assigns a week number to weeks, linking to my amazon table and telling the $ YA column to just count backwards -52 weeks.  This works but only if the ASIN exists in current year.  Any ASIN that exists in prior year but not current year gets ignored and my $ YA measure is understated. Pictures of date table and Amazon sales table below. Link to Amazon data snippet here : https://we.tl/t-FhIAU7K2ey

 As you can see in the line chart below the 2 spikes in dark blue (YA) are not as high as in the light blue which should technically represent the same data.

Thank you!

[ESemba22]

The link expired here is updated link: https://we.tl/t-T1AZT8PoYP

[Anonymous]

Hi @ESemba22 

I did some tests and given I have so little information, I can not reproduce the issue you met.

Please show the Measure that you are using in the visuals, how your visuals were created and which fields were used and the expected result. So that we can help you better.

Best Regards

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

[ESemba22]

Below is my $ YA Calculation

$ YA =

VAR CurrentRowNumber = 'AMAZON'[Week Number]

VAR TargetRowNumber = CurrentRowNumber - 52

RETURN

    CALCULATE(

        MAX('AMAZON'[$ Sales]),

        FILTER(

            'AMAZON',

            'AMAZON'[(Child) ASIN] = EARLIER('AMAZON'[(Child) ASIN]) &&

            'AMAZON'[Week Number] = TargetRowNumber

        )

    )

I am using the Line Chart visual and Week Ending/$ Sales/$ YA for my measures

 Thank you!

[Hahb]

Thanks