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,
I am using Power BI (Direct Query) from a SQL Source, and am having problems doing a Closing Stock Balance.
I have in a SQL Table the following structure
Date | Entry Type | Quantity | ItemID | LocationID |
31/05/2018 | Take On | 1000 | 1 | 10 |
01/06/2018 | Sales | -10 | 1 | 10 |
01/06/2018 | Purchase | 5 | 1 | 10 |
I am after a Closing Stock Calculation, that on 01/06/2018 displays for Item 1 and Location 10 the stock, currently when i select Quantity on 01/06/2018 it displays 5, i need it to display 1005 as to take in the closing balance from the beginning rather than limit the scope.
I have a Date Table also that is linked to the Date Column, as well as a Item and Location Dimension that is linked by the ID's on my stock table.
Thanks
Dan
Solved! Go to Solution.
Hi Dan,
I think something like the following might help you achieve what you want (although difficult to be certain without knowing your model):
Closing Stock Balance =
CALCULATE (
SUM ( Table1[Quantity] ),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)
Obviously, you'll need to change any table or column names to your own.
Hope that helps,
Thanks
Alex
Hi Dan,
Quick question - is the result you want to see 1005 or 995 (including sales)?
Cheers
Alex
Hi Alex,
I want 1005 on the 01/06/2018 and 1000 to be returned on the 31/05/2018 does that make sense?
Thanks
Dan
So sales are not included in your calculation?
Also, what result would you like to see? I'm imagining a visual metric for total sales whereby you filter on one date to see "Closing Stock" for that data, but would be helpful if you can confirm
Thanks
Alex
Hi Alex,
Apologies it should be 995, not 1005.
Does that help?
In regards to teh Visual i would in the report select a Reporting Date and the Report (Visual will display say bar chart with location or item )
Thanks
Dan
Hi Dan,
I think something like the following might help you achieve what you want (although difficult to be certain without knowing your model):
Closing Stock Balance =
CALCULATE (
SUM ( Table1[Quantity] ),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)
Obviously, you'll need to change any table or column names to your own.
Hope that helps,
Thanks
Alex
HI @jamesdanuk,
You can add calculate column to replace entry type to 'stock in' and 'stock out', then remove 'data', 'entry type' and add above column to show summary amount.
Regards,
Xiaoxin Sheng
Hi Xiaoxin,
I am unsure what you mean? are you able to demostrate how to do this ?
Thanks
Dan
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |