## Desktop

Frequent Visitor
Posts: 5
Registered: ‎01-18-2019

# Running Stock total by SKU by date

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:

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.

Accepted Solutions
Highlighted
Super User
Posts: 10,499
Registered: ‎07-11-2015

## Re: Running Stock total by SKU by date

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

Column =
RETURN

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

Proud to be a Datanaut!

All Replies
Super User
Posts: 10,499
Registered: ‎07-11-2015

## Re: Running Stock total by SKU by date

So what is the expected output for that example data?

Proud to be a Datanaut!

Frequent Visitor
Posts: 5
Registered: ‎01-18-2019

## Re: Running Stock total by SKU by date

Hi Greg,

Thanks for taking the time.

The expected output is the last colum - Balance.

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?

Highlighted
Super User
Posts: 10,499
Registered: ‎07-11-2015

## Re: Running Stock total by SKU by date

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

Column =
RETURN

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

Proud to be a Datanaut!

Frequent Visitor
Posts: 5
Registered: ‎01-18-2019

## Re: Running Stock total by SKU by date

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?

Super User
Posts: 10,499
Registered: ‎07-11-2015

## Re: Running Stock total by SKU by date

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