Addcolumns Calculated Column - trying to use row context

[Anonymous]

I can't figure out why I can't add a calculated column using ADDCOLUMNS where I remove the filter context of RECID and STARTDATE, keeping IMPORTDATETIME, ITEMID, and CUSTACCOUNTID as filters, and Sum the field 'RemSalesQty' for all RECID's that are less than the current row being calculated. I've tried a number of different ways and can't get it working, if I do get the current row filter context removed, it sums the entire table, not keeping the import/item/customer info as filters. 

SumRem and SumRem2 are trying to accomplish the same thing, I'm just showing 2 of the many ways I've tried - Everything returns blank.

var NewTable = ADDCOLUMNS(SUMMARIZE(FILTER(SalesOrderHistory,DATEVALUE(SalesOrderHistory[IMPORTDATETIME])>=DATE(YEAR(today())-1,MONTH(today()),1)),SalesOrderHistory[IMPORTDATETIME],SalesOrderHistory[CUSTACCOUNTID],Customer[CustomerName],SalesOrderHistory[ITEMID],SalesOrderHistory[RECID],SalesOrderHistory[STARTDATE]),

"SumRem",

CALCULATE(SUM(SalesOrderHistory[RemSalesQty]),ALL(SalesOrderHistory),SalesOrderHistory[IMPORTDATETIME]=SELECTEDVALUE(SalesOrderHistory[IMPORTDATETIME]),SalesOrderHistory[ITEMID]=SELECTEDVALUE(SalesOrderHistory[ITEMID]),SalesOrderHistory[RECID]<SELECTEDVALUE(SalesOrderHistory[RECID])),

"SumRem2",

CALCULATE(SUM(SalesOrderHistory[RemSalesQty]),ALL(SalesOrderHistory[RECID]),SalesOrderHistory[RECID]<SalesOrderHistory[RECID],ALL(SalesOrderHistory[STARTDATE])))

Return

NewTable

[Anonymous]

All - Below is an image from what I'm trying to do, filtered down to one particular ImportDate and Item to show the expected result. If I remove the statment underlined in red, I get 880 for all rows which is what I'd expect, but why can't I re-establish a filter using the RECID - I've tried using SELECTEDVALUE(),MIN() and all sorts of combinations to re-apply a filter using that row's RECID but nothing works.

@Greg_Deckler 

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Try EARLIER function in the calculated column.

Eexpected Result = CALCULATE(SUM('Table'[RemSalesQty]),FILTER('Table',[RECID]<EARLIER('Table'[RECID])))

Best Regards,

Stephen Tao

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

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Here is a very small sample, I've included the field 'RemSalesQty' so you can see what I'm trying to sum:

ImportDate	CustAccount	Customer	ItemId	RecID	StartDate	RemSalesQty	SumRem
12/1/2021	1494	BOB	1567	564106299	12/15/2021	4
12/1/2021	1494	BOB	1567	564106300	12/27/2021	2	4

So when I use ADDCOLUMNS to add SumRem, I'd like to keep the filters on SalesOrderHistory coming from the current row values for ImportDate, CustAccount, Customer, and ItemID  and sum the RemSalesQty for all RECID's that are LESS THAN the current row, so as you see 564106300 has a sum of 4. I've done this many times using measures, but now using ADDCOLUMNS with SUMMARIZE to create a Calculated Table, there seems to be an issue - it's as if I can't keep the filters on SalesOrderHistory given values from the current row.. I've tried ALL(SalesOrderHistory) and reapplying the filters one by one, didn't work. I've tried ALLEXCEPT(), and many other different combinations and I keep either getting blank for everything or it sums the entire table, no filters. 

[Anonymous]

@Greg_Deckler  Please see above