• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Calculate Daily Ending Inventory|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Calculate Daily Ending Inventory|Power Pivot|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotCalculate Daily Ending Inventory
sp_PrintTopic sp_TopicIcon
Calculate Daily Ending Inventory
Avatar
Carolyn Rainaud
Member
Members
Level 0
Forum Posts: 35
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
1
April 26, 2021 - 4:47 am
sp_Permalink sp_Print

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOnlineSmall Online
2
April 26, 2021 - 9:26 am
sp_Permalink sp_Print

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

Avatar
Carolyn Rainaud
Member
Members
Level 0
Forum Posts: 35
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
3
April 26, 2021 - 11:26 pm
sp_Permalink sp_Print

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.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOnlineSmall Online
4
April 27, 2021 - 9:31 am
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Carolyn Rainaud
Member
Members
Level 0
Forum Posts: 35
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
5
April 27, 2021 - 10:29 pm
sp_Permalink sp_Print

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
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Mynda Treacy, em em, Kathleen Silva
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 215
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Ngoc Qui Nguyen
Clement Mansfield
Rose .
Bindu Menon
Baruch Zemer
Forum Stats:
Groups: 3
Forums: 24
Topics: 6353
Posts: 27778

 

Member Stats:
Guest Posters: 49
Members: 32319
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.