Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Addcolumns Calculated Column - trying to use row context

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
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

       

      ImportDateCustAccountCustomerItemIdRecIDStartDateRemSalesQtySumRem
      12/1/20211494BOB156756410629912/15/20214 
      12/1/20211494BOB156756410630012/27/202124

       

      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's avatar
    Anonymous
    Not applicable
    4 years ago

    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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.