Calculated columns for previous weeks values

akfir — Thu, 29 Jun 2023 18:36:52 GMT

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

Re: Calculated columns for previous weeks values

v-zhangti — Mon, 03 Jul 2023 06:05:48 GMT

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.

Re: Calculated columns for previous weeks values

Lexporate — Mon, 25 Mar 2024 13:26:12 GMT

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

topic Calculated columns for previous weeks values in DAX Commands and Tips

Calculated columns for previous weeks values

Re: Calculated columns for previous weeks values

Re: Calculated columns for previous weeks values