Forum Discussion

Yashh's avatar
Yashh
Helper II
3 years ago
Solved

Incemental Subtraction

Hello All,   Currently I am having this view in Power BI Sales document Material Ordered Quantity Stock.Available Quantity Remaining Stock 2744570 105980 56 15331 15275 2793574 1...
Need Help
Show and Tell
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    Yashh 

    pls try to create an index column and  create two calculated columns

    stock available = 
VAR _index = CALCULATE(min([Index]),ALLEXCEPT('Table','Table'[Material]))
return [Stock.Available Quantity]-sumx(FILTER('Table','Table'[Material]=EARLIER('Table'[Material])&&[Index]<=EARLIER('Table'[Index])&&'Table'[Index]>_index),'Table'[Ordered Quantity])



remaining stock = [Stock.Available Quantity]-sumx(FILTER('Table','Table'[Material]=EARLIER('Table'[Material])&&[Index]<=EARLIER('Table'[Index])),'Table'[Ordered Quantity])

     

ryan_mayu's avatar
ryan_mayu
Super User
3 years ago

Yashh 

pls try to create an index column and  create two calculated columns

stock available = 
VAR _index = CALCULATE(min([Index]),ALLEXCEPT('Table','Table'[Material]))
return [Stock.Available Quantity]-sumx(FILTER('Table','Table'[Material]=EARLIER('Table'[Material])&&[Index]<=EARLIER('Table'[Index])&&'Table'[Index]>_index),'Table'[Ordered Quantity])



remaining stock = [Stock.Available Quantity]-sumx(FILTER('Table','Table'[Material]=EARLIER('Table'[Material])&&[Index]<=EARLIER('Table'[Index])),'Table'[Ordered Quantity])