Forum Discussion

maheshsinha's avatar
maheshsinha
New Member
9 years ago

Inventory Movement Analysis from Single Table

Hi All

 

I am new to Power BI and trying to learn and implement it in my company

 

The issue is i do have a table in SQL in which i do have records as below

Example Data

StoreCode | EntryType | Entry Code | Entry Date | Item Code | Qty

001 | PUR | 12331 | 01-APr-2017| 1001 | 100

001 | SAL | 12354 | 10-Apr-2017 | 1001 | -10

001 | Ret | 12454 | 15-Apr-2017 | 1001 | 1

 

So if we see 100 was purchased 10 sold and 1 got returned from customer that means closing is 91 where as if you see stock on 14-APr-2017 the closing will be 90.

Now problem is i can not use Entry Type and Measure of Closing at same time in a report where as to analyze the movement i need all these fields i.e Opening, Inward, Outward, CLosing

 

Please suggest how i can go ahead 

 

Regards

Mahesh

 

4 Replies

  • fhill's avatar
    fhill
    Resident Rockstar
    9 years ago

    Consider adding a Running Total Column as referenced in this link:

     

    https://community.powerbi.com/t5/Desktop/Running-Total-DAX/td-p/41393

     

    Here's my code based on your data:

    Running Total COLUMN =
    CALCULATE (
    SUM ( 'sample'[ Qty] ),
    ALLEXCEPT ( 'sample', 'sample'[ Item Code ] ),
    'sample'[ Entry Date ] <= EARLIER ( 'sample'[ Entry Date ] )
    )

     

    You can then graph this runnign total to show Inventory over time...  (I would suggest a Store Level filter, as I didn't exclude StoreCode from the running totals.)

     

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi maheshsinha,

    Have you resolved your issue? IF you have, please mark the helpful or right reply as answer. So other people will find the workaround clearly.

    Best Regards,
    Angelia

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi maheshsinha，

        I an very glad your issue has been resolved. Please mark the helpful reply as answer, or welcome to share your own workarond, so that more people has the similar problems will find the solution clearly and easily.

        Best Regards,
        Angelia