Calculated columns for previous weeks values

[akfir]

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

[v-zhangti · Accepted solution]

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]

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