Forum Discussion

sPowerBi's avatar
sPowerBi
Frequent Visitor
8 years ago

Calculate NPI (non-productive Inventories in DAX

Hi,

I want to calculate NPI ( non productive Inverntories) on power bi. I have stock movements table like this.

DateProduct CodeQuantityPrice
1.01.2018B510
2.01.2018B610
3.01.2018B710
4.01.2018B810
5.01.2018B34511
6.01.2018B-2212
7.01.2018B-7612
8.01.2018B24312
9.01.2018B311
########B-6311
########B1212

I must do calculation, find result  such as bellow

DateProduct CodeQuantityPrice 
1.01.2018B510 
2.01.2018B610 
3.01.2018B710 
4.01.2018B810 
5.01.2018B34511210
6.01.2018B-22120
7.01.2018B-76120
8.01.2018B24312243
9.01.2018B3113
10.01.2018B-63110
11.01.2018B121212
  Total Stock468

 

Thanks

8 Replies

  • sPowerBi's avatar
    sPowerBi
    Frequent Visitor
    8 years ago

    Hi,

    I want to calculate NPI ( non productive Inverntories) on power bi. I have stock movements table like this.

    DateProduct CodeQuantityPrice
    1.01.2018B510
    2.01.2018B610
    3.01.2018B710
    4.01.2018B810
    5.01.2018B34511
    6.01.2018B-2212
    7.01.2018B-7612
    8.01.2018B24312
    9.01.2018B311
    ########B-6311
    ########B1212

    I must do calculation, find result  such as bellow

    DateProduct CodeQuantityPrice 
    1.01.2018B510 
    2.01.2018B610 
    3.01.2018B710 
    4.01.2018B810 
    5.01.2018B34511210
    6.01.2018B-22120
    7.01.2018B-76120
    8.01.2018B24312243
    9.01.2018B3113
    10.01.2018B-63110
    11.01.2018B121212
      Total Stock468

     

    Thanks

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi sPowerBi,

      What's your calculation rules? How did you get the following column? Please list an example for more details, otherwise we can not help you.



      Thanks,
      Angelia

      • sPowerBi's avatar
        sPowerBi
        Frequent Visitor
        8 years ago

        Hi,

        This  is movement table. And total stock quantity equal 468 on 11 January 2018. 

        If the quantity is less than zero then the tansaction is the sales movement. If the quantity is greater than zero then the transaction is the buy movement. so I want to find which buying movements the stock consist of. I find out which buying movements by reducing Quantity of movement from total stock

        I hope i can tell.

        Thanks