Reply
Frequent Visitor
Posts: 5
Registered: ‎01-18-2019
Accepted Solution

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:

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.
 
PWRBI STOCK BAL.PNG
 
 

 


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 = 
VAR __table = FILTER('Table',[skuID] = EARLIER([skuID]) && [adjDate] <= EARLIER([adjDate]))
RETURN
SUMX(__table,[adjQty])

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


View solution in original post


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?


Did I answer your question? Mark my post as a solution!

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 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?

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 = 
VAR __table = FILTER('Table',[skuID] = EARLIER([skuID]) && [adjDate] <= EARLIER([adjDate]))
RETURN
SUMX(__table,[adjQty])

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


Did I answer your question? Mark my post as a solution!

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?

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

 

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)


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!