Forum Discussion

mageshraja's avatar
mageshraja
Frequent Visitor
6 years ago
Solved

Multiple line Qty Subtraction from Single Data

Hi , Hope this would be simple doubt. But I need help to crack the solution in Power bi Have Two tables: Table 1: Same part # stock received on muliple dates / Unique Ref.  Table 2: Usage of the ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try like new columns

    Col1 = sumx(filter(table1, table1[Part #]=earlier(table1[Part #]) && table1[Unique Ref ID]<=earlier(Unique Ref ID])),table[Received Qty])
col2 = sumx(filter(table2, table1[Part #]=table2[Part #]),table2[Qty Used])
col3 = if((col2-col1)<col2,(col2-col1),col2)

     

    Appreciate your Kudos.

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

First, can you guarantee that Unique Ref ID will always sort alphabetically such that the earliest "instances" are always alphabetically "less" than later "instances". If that cannot be guaranteed, do you have a date column or index column where you can distinguish "earlier" from "later"?

 

Because if you don't, this will be difficult if not impossible.

 

mageshraja's avatar
mageshraja
Frequent Visitor
6 years ago

Hi Greg,

Yes , the unique ref ID are sequenced numeric data which can be sorted from earliest. In that case what solution can support in this case.

 

Thanks,

Magesh