Lookup Value from same table with multiple criteria

[abhishekrws01]

Hello,

I am trying to align the previous year's week num & weekday sales to the current year's weekNum and weekday sales for each product & country. I have built a calculated table below and would like to add the last column, "NextYearFC". I tried the summarize and lookup value functions but could not achieve what I wanted. Any tip/clue will be helpful, please. 

Thank you 

[amitchandak]

@abhishekrws01 , If you have Year and week num, or date then have a separate Date/yearweek table with Year week, year, and week

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

[abhishekrws01]

Thank you