How to calculate stock at the start of the month, and the end of the month, and get the difference?

[Anonymous]

I have a the following table:

There is a date for when the single item was added to stock, and a date for when it was sold. 

I would like to find out what the stock was at the start of each month, and the end of each month. And then calculate the difference. 

This will allow me to track which products are selling the fastest from month to month. Sort of like this:

I have approached it by using running totals, by adding a column of 1's that I could use a running total on. I had to add a separate column of 1's for sold, as some of them are not sold yet (there are nulls in the "Sold" column) hence there will be some zeros.

I get the running total for products added to stock, and running total for products sold:

Cumulative Stock = 
CALCULATE(  
	SUM(Data[Qty Added to Stock]),
	FILTER(
		ALLSELECTED(CalendarTable[Date]),
		ISONORAFTER(CalendarTable[Date], MAX(CalendarTable[Date]), DESC)
        
	)
)

Note for this one I use an inactive relationship:

Cumulative Sold = 
CALCULATE(  
	SUM(Data[Qty Sold]), USERELATIONSHIP(CalendarTable[Date],Data[Sold]),
	FILTER(
		ALLSELECTED(CalendarTable[Date]),
		ISONORAFTER(CalendarTable[Date], MAX(CalendarTable[Date]), DESC)
        
	)
)

To get the inventory, I subtract Cumulative Stock - Cumulative Sold.

And with some various "month start" and "month end" flags I end up with this:

You can see in the table nothing was sold until Jan 2019. So the very first row has a big 2900% difference between start and end month as products are added to stock. Which is fine.

However, I moved this data into a bar chart, and it doesn't match up. See below where I hightlight it. There's a big difference. 

So that makes me wonder how robust these measures are ... perhaps I have approached it the wrong way. 

Any idea why the bar chart doesn't match the table? 

Also have I approached this the wrong way? Any advice on how to do this a more rubust or efficient way?

I am happy to share sample data and pbix file. Although I can't find any upload / attachment feature here.

[Anonymous]

@amitchandak 

You might be right, there are some missing dates in the Data[Sold] column. 

I have added a filter to remove blank dates: 

Cumulative Sold =
CALCULATE( CALCULATE(
SUM(Data[Qty Sold]), 

FILTER(Data, Data[Sold] <> BLANK()),

USERELATIONSHIP(CalendarTable[Date],Data[Sold])),
FILTER(
ALLSELECTED(CalendarTable[Date]),
ALLSELECTED(CalendarTable[Date]), CalendarTable[Date] <= max(CalendarTable[Date])

)
)

However now the table is almost all empty. 

There are only two entries in the cumulative sold. Any ideas?

I guess my problem is calculating two running totals, from the same table, but using different columns. 

I have an inactive relationship for cumulative sold:

And an active for Cumulative Stock:

[amitchandak]

@Anonymous , As the first number is big, I doubt there are some missing dates in date table

Also for begin inventory if should be < Min  and for end of month it should <= max

Cumulative Stock =
CALCULATE(
SUM(Data[Qty Added to Stock]),
FILTER(
ALLSELECTED(CalendarTable[Date]), CalendarTable[Date] < Min (CalendarTable[Date])

)
)

Cumulative Sold =
CALCULATE( CALCULATE(
SUM(Data[Qty Sold]), USERELATIONSHIP(CalendarTable[Date],Data[Sold])),
FILTER(
ALLSELECTED(CalendarTable[Date]),
ALLSELECTED(CalendarTable[Date]), CalendarTable[Date] < Min (CalendarTable[Date])

)
)

end

Cumulative Stock =
CALCULATE(
SUM(Data[Qty Added to Stock]),
FILTER(
ALLSELECTED(CalendarTable[Date]), CalendarTable[Date] <= Max (CalendarTable[Date])

)
)

Cumulative Sold =
CALCULATE( CALCULATE(
SUM(Data[Qty Sold]), USERELATIONSHIP(CalendarTable[Date],Data[Sold])),
FILTER(
ALLSELECTED(CalendarTable[Date]),
ALLSELECTED(CalendarTable[Date]), CalendarTable[Date] <= max(CalendarTable[Date])

)
)

[amitchandak]

@Anonymous , As the first number is big, I doubt there are some missing dates in date table

Also for begin inventory if should be < Min  and for end of month it should <= max

Cumulative Stock =
CALCULATE(
SUM(Data[Qty Added to Stock]),
FILTER(
ALLSELECTED(CalendarTable[Date]), CalendarTable[Date] < Min (CalendarTable[Date])

)
)

Cumulative Sold =
CALCULATE( CALCULATE(
SUM(Data[Qty Sold]), USERELATIONSHIP(CalendarTable[Date],Data[Sold])),
FILTER(
ALLSELECTED(CalendarTable[Date]),
ALLSELECTED(CalendarTable[Date]), CalendarTable[Date] < Min (CalendarTable[Date])

)
)

end

Cumulative Stock =
CALCULATE(
SUM(Data[Qty Added to Stock]),
FILTER(
ALLSELECTED(CalendarTable[Date]), CalendarTable[Date] <= Max (CalendarTable[Date])

)
)

Cumulative Sold =
CALCULATE( CALCULATE(
SUM(Data[Qty Sold]), USERELATIONSHIP(CalendarTable[Date],Data[Sold])),
FILTER(
ALLSELECTED(CalendarTable[Date]),
ALLSELECTED(CalendarTable[Date]), CalendarTable[Date] <= max(CalendarTable[Date])

)
)

[Anonymous]

---

@amitchandak wrote:

@Anonymous, As the first number is big, I doubt there are some missing dates in date table

I'm sorry this is not very clear. Do I have a problem with my data?

To get inventory, I subtracted Cumulative Stock - Cumulative Sold. Could this be the problem?