Inventory Movement Analysis from Single Table

[maheshsinha]

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

[v-huizhn-msft]

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

[maheshsinha]

Yes thank you very much it is solved

[v-huizhn-msft]

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

[fhill]

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.)