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 to all the members of enthusiastic and supportive community.
I have to create two columns "closing stock" and "opening stock" columns based on "date", "purchased stock", and "sold stock".
The data sample is like below:
I need to get the table with calculated columns as below using DAX (in Power BI).
I have used these formulae to create calculated columns but causing Circular Dependency Error.
Opening Stock =
VAR PreviousRow =
TOPN (
1,
FILTER (
'Table1',
'Table1'[Date] < EARLIER ( 'Table1'[Date] )
),
'Table1'[Date], DESC
)
VAR PreviousValue =
IF(
MINX ( PreviousRow, 'Table1'[Closing Stock] ) = BLANK(),
[Purchased Stock],
MINX ( PreviousRow, 'Table1'[Closing Stock] )
)
RETURN
PreviousValue
---------------------------------
Closing Stock = IF(
[Purchased Stock] <> BLANK(),
[Purchased Stock] + [Opening Stock] - [Sold Stock],
[Opening Stock] - [Sold Stock]
)
I was able to get the logic for Opening Stock column from @Zubair_Muhammad. @Zubair_Muhammad, it would be great if you or anyone from the community helped me obtain the solution.
Please help me to arrive to a solution..
Thanks in Advance.
Solved! Go to Solution.
Please try these column expressions instead, replacing StockSales with your actual table name.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try these column expressions instead, replacing StockSales with your actual table name.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Pat ( @mahoneypat ), Thanks for the help recently.
I have a scenario where, the data is like this...
which is clear that the purchased stock value is not available. But can be obtained and accounted for manually with the help of inventory department (which cannot be obtained while creating the above table). and for the first row in opening stock I can enter the opening stock value manually in Excel (as in the image below) but the same thing when I did in PowerBI, the things are breaking. and getting the negative values.
Moreover, there are categories of stock to be accounted in this data, for example there are 3 categories of stock, whose count need to be tracked on perday basis. (not mandatory that each category of stock is sold on a particular date and also not mandatory that when stock is purchased all categories of stock are purchased on a particular date).
I would be very thankful if you could help me obtain solution for these. And help from any community members is verymuch invited.
Thanks in Advance.
Would this approach work, adding in an initial value? It is hardcoded below but it could be a dynamic expression with a value from another table you bring in.
Opening Stock =
VAR vInitValue = 100 // or use dynamic expression
VAR vThisDate = StockSales[Date]
VAR vPurchases =
CALCULATE (
SUM ( StockSales[Purchased Stock] ),
ALL ( StockSales ),
StockSales[Date] <= vThisDate
)
VAR vSales =
CALCULATE (
SUM ( StockSales[Sold Stock] ),
ALL ( StockSales ),
StockSales[Date] < vThisDate
)
RETURN
vInitValue + vPurchases - vSales
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks for the suggestion Pat ( @mahoneypat ).
It certainly helped me and there were a few corrections to be made at other (prior) stages in my data. Now result is as expected after dealing with those corrections and changes.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |