rolling sum by item with filter conditions

[Anonymous]

Hi all, 
I'm trying to create a calculated column that calculates the rolling sum for each item code depending on conditions:

- the sum from 5 weeks in the past up to the current week ( week <= current week -5)

- column source = Backlog / Transfer Out

- the year = current year

I have tried all the ways of using SUMX but I only get a unique value for all the column wich is not what I'm looking for:

the measure that I'm using is this one.

available stock (trial)=

SUMX(FILTER(

'RAW_DATA',

'RAW_DATA'[ITEM_CODE] = EARLIER('RAW_DATA'[ITEM_CODE]) && 'RAW_DATA'[WEEK] <= WEEKNUM(TODAY())-5 && ('RAW_DATA'[SOURCE] = "Backlog" || 'RAW_DATA'[SOURCE]= "Transfer Out")),RAW_DATA[Sum_QUANTITY])

what i'm doing wrong ?

thanks ! 

Jorge 

[v-yanjiang-msft]

Hi @Anonymous ,

Did my solution solve your problem? If yes, please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, if you want to calculate the rolling sum value, and your sample data are arranged by date, you should add a condition in your formula.

'RAW_DATA'[Date] <= MAX ( 'RAW_DATA'[Date] )

Is this your expected result?

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Syndicate_Admin]

Hi Thank you for your replay.

It is actually filtered by <= number of week in my first sample of code. I had no efect on my calculation

'RAW_DATA'[SEMANA] <= WEEKNUM(TODAY())-5

[v-yanjiang-msft]

Hi @Anonymous ,

So what value do you want the "available stock (trial)" get, is this your expected outcome?

Best Regards,
Community Support Team _ kalyj

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.