Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Subtracting rows

Hi All,

 

I would like to subtract lines in Power BI.

 

Substract lines based on product id and ship date. "After shipping "is calculated column which values are correct only for 1 line.

 

I would like to get below result.

 

Let me know if you need any informations.

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous ,

    We don't need to create Table2. Please group Material in Power BI Desktop query editor, then create the following columns in table1. For more details, please check attached PBIX file.

    shipcum = CALCULATE(SUM (Table1[To Ship] ),
    ALLEXCEPT( Table1,Table1[Material]),
    Table1[Index] <= EARLIER ( Table1[Index] ))
    Column = CALCULATE(MAX(Table1[Inventory on Hand]),ALLEXCEPT(Table1,Table1[Material]),Table1[Index]=1)
    After shipping = Table1[Column]-Table1[shipcum]



    Regards,
    Lydia

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous ,

    Please create the following columns in your table.

    Column = 
CALCULATE (
    SUM ( Table[To ship] ),
    ALLEXCEPT( Table,Table[Product]),
    Table[Date] <= EARLIER (  Table[Date] )
)
    after shipping = Table[Inventory]-Table[Column]



    Regards,
    Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Lydia,

       

      It looks like below, using above. Any idea what is wrong?

       

       

      //Rasty

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Anonymous ,

        Could you please export the data of your table to Excel and share me the Excel? You can replace confidential data with dummy data.

        Regards,
        Lydia