Running Stock total by SKU by date

[Anonymous]

Hi all,

New to DAX, but used Access, Excel, SQL for years.

I have a table of stock adjustments. Sales, receipts, manual adjustments, transefers, on hold, off hold etc.

I have created measures to identify Sales (based on adjCode includes SALE) and Receipts (base on adjCode includes GI). 

Now i want to calculate the stock balance of each product (skuID) on each date (adjDate).

I have tried several suggestions, but all seem to work on the principle that a product ID is numerical not text; This being the current formula:

CALCULATE(sum(qryGI_v_Sales[adjQty]),FILTER(ALL(qryGI_v_Sales),qryGI_v_Sales[adjDate] <= max(qryGI_v_Sales[adjDate]) && qryGI_v_Sales[skuID] <= max( qryGI_v_Sales[skuID])))

This is not returning the correct stock balances. 

I would need this measure to persist through filters/slicers on date and skuID so the stock balance displayed is correct at the date(s) shown.

The below is a sample of the data and how the balance measure should evaluate.

[Greg_Deckler]

So what is the expected output for that example data?

[Anonymous]

Hi Greg,

Thanks for taking the time.

The expected output is the last colum - Balance.

The data is the first 4 columns (adjDate, skuID, adjQty and adjCode). 

The measure needs to be calculating the value shown in the 5th column.

Essentially for any given row, i would be looking for the sum of all [adjQty] for that [skuID] upto and including the [adjDate]

Perhaps this should this be a column and not a measure?

[Greg_Deckler · Accepted solution]

This would be easiest as a column but you could do it as a measure.

Column = 
VAR __table = FILTER('Table',[skuID] = EARLIER([skuID]) && [adjDate] <= EARLIER([adjDate]))
RETURN
SUMX(__table,[adjQty])

Did that free-hand so be wary of syntax errors.

[Anonymous]

Greg many thanks, that works.

Tried it as a measure but got an error, so tried as a colum and worked fine.

Can i ask why your solution uses a variable? The below seems to work also?

SUMX(FILTER('Table',[skuID] = EARLIER([skuID]) && [adjDate] <= EARLIER([adjDate])),[adjQty])

[Greg_Deckler]

I like using VAR's in order to make things more readable. Too many bad past experiences with unreadable code. (Perl)