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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

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
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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