Inventory Calculation with power pivot

Question

Hi All,

I want to create inventory status report with power pivot. Usually I am doing with excel formula's.

I already have inventory transaction with power pivot, you can see table at below.

And I want to added Beginning Inventory and also SSR (I can do that for SSR with DAX).

FYI that there is no stock data at powerpivot table/database. TO get stock on hand, I calculated from Reception and other inventory transaction.

Can you help me ?

Thanks

Henry

Answer 1

Do you want to do something similar to this?

Stock End Of Period :=

    CALCULATE( SUM( [Units Balance] ), DATESBETWEEN( BLANK(), LASTDATE( Transactions[Movement Date] ) )

Stock Beginning Of Period :=

    CALCULATE( SUM( [Units Balance] ), DATESBETWEEN( BLANK(), ADDDATE( FIRSTDATE( Transactions[Movement Date] ), -1, DAY ) )

---

Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

Answer 2

What do you mean of unit balance ? is it unit of first of stock ?

because I don't have stock calculation column

please advice

I already tried and error with the function