Year over year comparison

[tbruce]

I am new to PowerBI.  I am trying to add a column to a table that provides the value for the same row, but LY.

The data is weekly with a week number in a custom format that include the year -- YYYYWW for example, 201801 is week 1 year 2018.

I found the following formula in another forum entry... and I modified it with fixed values and it returns the right data for the first row of the table in the picture. 

LY POS Sales = calculate(sum('POS Detail'[POS Sales]),Filter('POS Detail','POS Detail'[Prime Item Nbr]= 8358818),Filter('POS Detail','POS Detail'[WM Week]="201833"),Filter('POS Detail','POS Detail'[Store Nbr]=390))

I need to know how to modify the formula so that it provides the right data for the criteria in each row.   Or is there a better way to get a % change in a table that has the data stored as below. 

Thanks!

[edhans · Accepted solution]

The right way to do this is create a date table, then add a date column to your data in Power Query. This old post will help with that since you only have week and year.

Once you've done that, the SAMEPERIODLASTYEAR() function will return the dates you need. So, say your sales is the [Total Sales] measure.

Sales Last Year =
CALCULATE ( 
     [Total Sales], 
     SAMEPERIODLASTYEAR ( Dates[Date] )
)

Would return sales for last year in the same context as your visual. So if your visual is showing a full year, it will be the full LY data. If it is by quarter, week, or day, it will still be the samething for last year.