Forum Discussion
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 | 105980 | 56 | 15331 | 15275 |
| 2744571 | 105980 | 56 | 15331 | 15275 |
| 2793575 | 105980 | 56 | 15331 | 15275 |
But I want to have this view
| Sales document | Material | Ordered Quantity | Stock.Available Quantity | Remaining Stock |
| 2744570 | 105980 | 56 | 15331 | 15275 |
| 2793574 | 105980 | 56 | 15275 | 15219 |
| 2744571 | 105980 | 56 | 15219 | 15163 |
| 2793575 | 105980 | 56 | 15163 | 15107 |
How can I do it and also i have many materials so for every material I have to do it.
I will give an example of the different materials i have
| Sales document | Material | Ordered Quantity | Stock.Available Quantity |
| 2744570 | 105980 | 56 | 15331 |
| 2793574 | 105980 | 56 | 15331 |
| 2744571 | 105980 | 56 | 15331 |
| 2793575 | 105980 | 56 | 15331 |
| 2787998 | 1056446 | 1 | 3 |
| 2792122 | 1056446 | 1 | 3 |
| 2793918 | 1056446 | 1 | 3 |
| 2786859 | 522227 | 2 | 4548 |
| 2786860 | 522227 | 2 | 4548 |
| 2786861 | 522227 | 2 | 4548 |
| 2784586 | 726326 | 14 | 109 |
| 2793211 | 555153 | 1 | 186 |
| 2796889 | 555153 | 1 | 186 |
| 2796871 | 555153 | 1 | 186 |
| 2796492 | 555153 | 1 | 186 |
| 2796580 | 555153 | 1 | 186 |
| 2796918 | 555153 | 1 | 186 |
| 2796890 | 555153 | 1 | 186 |
Please help me with this.
BR
Yashh Gaikwad
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])
1 Reply
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])