Log In
Register
Home
April 21, 2015
Offline
Hi,
I'm using Excel Power Query and Power Pivot to track 3 systems and 25 products for tons in, tons processed and tons out.
My challenge is to create a table with the quantities in out and the daily ending inventory for these 25 products.
Attached is a sample file with one product. For this, there are two fact tables, one for processed tons and one for outbound tons, and one date table. The measure for Power Pivot I can't seem to calculate is the daily ending inventory. I've "added it" by using an excel formula on the PT tab to show what I'm looking to do.
I'd like to be able to use slicers on these inventory pivot tables, but of course that won't work with an excel formula "add in".
Any help is greatly appreciated. Thanks again!
Carolyn
See Answer
July 16, 2010
Offline
Hi Carolyn,
Thanks for sharing your file. There's no information as to where the opening balance is coming from, so I couldn't incorporate that, but you can use the following formula to get a running total of the Net Change:
=CALCULATE([Net Change],
FILTER(
ALLSELECTED('Date'[Date]),
ISONORAFTER('Date'[Date], MAX(OutShipped[Date]), DESC)
)
)
You'll also need a dimension table for the products that you want in your Slicer to filter.
Mynda
April 21, 2015
Offline
Morning Mynda,
The measure works great. Thank you for your quick response. I spent most of the weekend trying to find something that would work.
There are a couple of other questions, I'd like to show daily inventory value even if there are no transactions. The measure only shows a result if Outshipped has a date. See updated sample file (col F of PT tab).
I will be looking to create a summary of month ending balances for all of these 25 product inventories.
Additionally, any suggestions on how to get the beginning balance inserted? I could create a record in processed tons dated 12/31/2020 for 1,446.00? Do you have any better ideas?
Thanks again.
July 16, 2010
Offline
Hi Carolyn,
Just change the date field the ISONORAFTER is referring to:
=CALCULATE([Net Change],
FILTER(
ALLSELECTED('Date'[Date]),
ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
)
)
If you only need something crude you can just add a table with the inventory brought forward amount and then add it to the measure like so:
=CALCULATE([Net Change], FILTER( ALLSELECTED('Date'[Date]), ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC) ) )+SUM(InventoryOpenBal[Inventory BF])
But if you need it filtered by different products then your brought forward amounts will need to be listed by product at the least.
Mynda
Answers Post
April 21, 2015
Offline
Dear Mindy,
Sometime you just need someone to take a fresh look to get steered in the right direction. The measure results are perfect.
Can't thank you enough for the help. It's great to know you and your team are always available for support.
Have a great day!
Carolyn
The following users say thank you to Carolyn Rainaud for this useful post:
Mynda Treacy
1 Guest(s)
