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

[Anonymous]

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

[amitchandak]

Check

https://community.powerbi.com/t5/Desktop/Inventory-Calculation/td-p/138540

https://community.powerbi.com/t5/Desktop/Calculate-inventory-on-a-given-date/td-p/71699

[Anonymous]

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]

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

[Anonymous]

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?