Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
bpratt
Frequent Visitor

How to set up beginning and end of period inventory?

I have table with inventory for each day. I want to set up a dashboard card in which I can select a date range and the summed inventory amount for the for the first date in the range will be displayed. Similarily I'd like to be able to display the summed inventory for the last date in the period would be displayed. What calculation or combination of calculations can I use to do this?

Thanks.

1 ACCEPTED SOLUTION

Thank you for your replies. Konstantions, I do have a date table in my model.  When creating a measure with your solution I got an error saying the MIN funcation cannot be used in the filter expression. From the article you linked, which is great, I found this solution that worked perfectly for me. 

 

BOP Units = CALCULATE(SUM(Inventory[OnHandQuantity]),FIRSTDATE(Dimension_Date[Date]))

 

 

Thanks again. 

View solution in original post

5 REPLIES 5
JasonKarlaile
New Member

Begin with the balance sheet from the end of the last accounting period.
Check if any other goods or inventory was purchased between when ending inventory was taken and when you start calculating beginning inventory.Plug the numbers into the equation and calculate the beginning inventory. I am newbie here just wanted to help you guys for the corncernd problem well I had taken all help from the https://goo.gl/VS81cM and it worked for me.May be it will be useful for you too.

JasonKarlaile
New Member

Begin with the balance sheet from the end of the last accounting period.
Check if any other goods or inventory was purchased between when ending inventory was taken and when you start calculating beginning inventory.
Plug the numbers into the equation and calculate the beginning inventory. I am newbie here just wanted to help you guys for the corncernd problem well I had taken all help from the  and it worked for me.May be it will be useful for you too.

Greg_Deckler
Super User
Super User

Create a New Measure where:

 

Measure = SUM([Inventory Amount])

 

Put the measure in a table along with maybe the date and whatever other fields. Add a filter for date, used Advanced Filtering to filter to a range. Date greater than x and less than y.

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

First you need to have a Date table with a relantionship with the inventory table..

First day = CALCULATE ( SUM( InventoryAmout) ; FILTER ( Date Table ; Date Table[Date] = MIN (Date Table[Date])))

For the last date change MIN to MAX..

 

Else there are certain inventory formulas like CLOSINGBALANCEMONTH ect..

 

Great Article in SQLBI for many inventory measures https://www.sqlbi.com/articles/inventory-in-power-pivot-and-dax-snapshot-vs-dynamic-calculation/

Konstantinos Ioannou

Thank you for your replies. Konstantions, I do have a date table in my model.  When creating a measure with your solution I got an error saying the MIN funcation cannot be used in the filter expression. From the article you linked, which is great, I found this solution that worked perfectly for me. 

 

BOP Units = CALCULATE(SUM(Inventory[OnHandQuantity]),FIRSTDATE(Dimension_Date[Date]))

 

 

Thanks again. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.