Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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.
So what is the expected output for that example data?
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?
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.
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])
I like using VAR's in order to make things more readable. Too many bad past experiences with unreadable code. (Perl)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |