cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jhumland Frequent Visitor
Frequent Visitor

Inventory (backwards): sum up purchase and sales values before a specific end date [Beginner]

Starting Position:

  • I have a snapshot of the current inventory stocks (specific date)
  • I have daily historical data (up to now) on purchases and sales
  • I have already linked these 3 data sources by means of a date table

Goal:

  • backward calculation of inventory levels by "re"-adding sales and subtracting purchases
  • main output: a bar chart showing the daily/weekly inventory stocks for the last 2 years up to now

Approach:

  • I need a measure summing up the sales values from every respective date in the past up to the date of the stock snapshot
  • I need a measure summing up the purchase values from every respective date in the past up to the date of the stock snapshot
  • Then, for ever respective date, I simply need to calculate: 
    • Current snapshot of Inventory Level - summed up purchases - summed up sales

 

Problem:

  • how do I formulate a measure that calculates these daily inventory levels

 

NB: I already tried DATESBETWEEN but failed to keep the start date dynamic

 

4 REPLIES 4
amitchandak Super Contributor
Super Contributor

Re: Inventory (backwards): sum up purchase and sales values before a specific end date [Beginner]

jhumland Frequent Visitor
Frequent Visitor

Re: Inventory (backwards): sum up purchase and sales values before a specific end date [Beginner]

I have already checked out these threads, but the issue I'm faced with is a bit different:

  • I do not have opening balances
  • I do not want to calculate changes in balance

 

Based on an overview of the current balances (with specific date X) I need to reconstruct the past balances (daily) by creating a measure that's subtracting the sum of purchases and adding the sum of sales that happened between each day and the date X

amitchandak Super Contributor
Super Contributor

Re: Inventory (backwards): sum up purchase and sales values before a specific end date [Beginner]

If date if fixed by a slicer then 

 

 

Inventory till date = 
Var _min_date_for_inventory = CALCULATE(maxx(all('Date'[Date Filer]),'Date'[Date Filer]))
Var _Todays_date=CALCULATE(maxx(ALLSELECTED('Date'[Date Filer]),'Date'[Date Filer]))

Var _inventory= CALCULATE(sum(Sales[Sales Amount]),all(Sales[Sales Date].[Date]),Sales[Sales Date]<=_min_date_for_inventory)
Var   Sale_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))
Var   purchase_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))

return
_inventory -Sale_till_tody+purchase_till_tody

 

 

_inventory is optional , can be removed

jhumland Frequent Visitor
Frequent Visitor

Re: Inventory (backwards): sum up purchase and sales values before a specific end date [Beginner]

will it work if the end date is fixed but the start date is variable?

 

Because the goal is to have a bar chart with the days on the x-axis which are the respective start dates.

 

For example:

  • current inventory balance date (end date): 09/18/2019
  • so how can I show the respective calculated inventory balance values for all previous days (every day is a new start day) if i need to fix the date by a slicer?

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 137 members 1,677 guests
Please welcome our newest community members: