Forum Discussion

abhishekrws01's avatar
abhishekrws01
Helper I
3 years ago

Lookup Value from same table with multiple criteria

 

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 

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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])))