Forum Discussion

akfir's avatar
akfir
Helper V
3 years ago
Solved

Calculated columns for previous weeks values

Hi guys,
I have a long table of weekly shares (Share) of several products (SKU) in different regions (Region) as below with the yellow column names. I wish to add 3 calculated columns which present the previous week (-1 WEEK) share of the same product in the same region - same thing for the week before (-2 WEEK) and before that (-3 WEEK).

Please help.

Thanks

 

Calculated columns
previous week
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, akfir 

     

    You can try the following methods.

    Column:

    Rankweek = RANKX(FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])),[Year_Week],,ASC)
    -1 Week = 
Var _week1=MAXX(FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])&&[Rankweek]<EARLIER('Table'[Rankweek])),[Rankweek])
Return
CALCULATE(SUM('Table'[Share]),FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])&&[Rankweek]=_week1))
    -2 Week = 
Var _week2=MAXX(FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])&&[Rankweek]<EARLIER('Table'[Rankweek])-1),[Rankweek])
Return
CALCULATE(SUM('Table'[Share]),FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])&&[Rankweek]=_week2))
    -3 Week = 
Var _week3=MAXX(FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])&&[Rankweek]<EARLIER('Table'[Rankweek])-2),[Rankweek])
Return
CALCULATE(SUM('Table'[Share]),FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])&&[Rankweek]=_week3))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, akfir 

     

    You can try the following methods.

    Column:

    Rankweek = RANKX(FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])),[Year_Week],,ASC)
    -1 Week = 
Var _week1=MAXX(FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])&&[Rankweek]<EARLIER('Table'[Rankweek])),[Rankweek])
Return
CALCULATE(SUM('Table'[Share]),FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])&&[Rankweek]=_week1))
    -2 Week = 
Var _week2=MAXX(FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])&&[Rankweek]<EARLIER('Table'[Rankweek])-1),[Rankweek])
Return
CALCULATE(SUM('Table'[Share]),FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])&&[Rankweek]=_week2))
    -3 Week = 
Var _week3=MAXX(FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])&&[Rankweek]<EARLIER('Table'[Rankweek])-2),[Rankweek])
Return
CALCULATE(SUM('Table'[Share]),FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Region]=EARLIER('Table'[Region])&&[Rankweek]=_week3))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

    • Lexporate's avatar
      Lexporate
      Frequent Visitor
      2 years ago

      This is the best solution (in my opinion) to fill the column with previous value