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 everyone-
I am new to Power BI and hoping that someone would be able to help me with a Power BI desktop column calculation.
Here is a sample of the data table I am working with:
Item | Month | Supply | Demand | On Hand Inventory (as on Jan 1) | Starting inventory | Ending inventory |
A | 1/1/2019 | 16,727 | 7,396 | 32,944 | 32,944 | 42,275 |
A | 2/1/2019 | 0 | 8,700 | |||
A | 3/1/2019 | 0 | 10,300 | |||
A | 4/1/2019 | 37,000 | 10,140 | |||
A | 5/1/2019 | 18,500 | 11,220 | |||
A | 6/1/2019 | 0 | 9,940 | |||
A | 7/1/2019 | 37,000 | 8,380 | |||
A | 8/1/2019 | 0 | 16,936 | |||
A | 9/1/2019 | 0 | 7,100 | |||
A | 10/1/2019 | 0 | 9,560 | |||
A | 11/1/2019 | 0 | 12,971 |
I need to calculate starting and ending inventories in each month for an item.
For the first month, the Ending inventory calculation is straight forward : On Hand inventory + Supply - Demand but for the subsequent months, the ending inventory of prior month will become starting inventory of the current month and the ending inventory of that month will need to be calculated as 'ending inventory of prior month + supply in that month - demand in that month'.
I tried one approach where i calculated previous month's ending inventory by creating Row ID using RANKX, and then another column (Prior Row ID) using EARLIER to get the previous Row ID. I then used this Prior Row ID to calculate ending inventory of prior month:
Solved! Go to Solution.
I'm not sure how you could get #rows times the starting inventory. However there are some issues with the previous expressions I post, but I think the general approach is still the correct one.
These two measures appear to do the trick
Starting Inventory2 = var _priorMonths = FILTER( ALL(Table1[Month]), Table1[Month] < MAX(Table1[Month] )) var _priorAndCurrentMonths = FILTER( ALL(Table1[Month]), Table1[Month] <= MAX(Table1[Month] )) RETURN CALCULATE(SUM(Table1[On Hand inventory (as of Jan 1)]),_priorAndCurrentMonths) + CALCULATE(SUM( Table1[Supply]) -SUM( Table1[Demand]) , _priorMonths )
Ending Inventory2 = var _priorMonths = FILTER( ALL(Table1[Month]), Table1[Month] <= MAX(Table1[Month] )) RETURN CALCULATE(SUM(Table1[On Hand inventory (as of Jan 1)]) + SUM( Table1[Supply]) -SUM( Table1[Demand]) , _priorMonths )
If your data is exactly how you have it pictured below you should be able to create measures to calculate the Starting and Ending Inventory using the first 3 columns. You should be able to do this without recursion just by adding up the lifetime to date values for supply and demand. The only difference between the start and end inventory is the _priorMonths variable where start is less than the current month and end is less than or equal to the current month.
eg.
Starting Inventory =
var _priorMonths = FILTER( ALL(Table1[Month]), Table1[Month] < MAX(Table1Month] )
var _priorSupply = CALCULATE( SUM( Table1[Supply] , _priorMonths )
var _priorDemand = CALCULATE( SUM( Table1[Demand] , _priorMonths )
RETURN SUM(Table1[ On Hand Inventory (as on Jan 1)]) + _priorSupply - _priorDemand
Ending Inventory =
var _priorMonths = FILTER( ALL(Table1[Month]), Table1[Month] <= MAX(Table1Month] )
var _priorSupply = CALCULATE( SUM( Table1[Supply] , _priorMonths )
var _priorDemand = CALCULATE( SUM( Table1[Demand] , _priorMonths )
RETURN SUM(Table1[ On Hand Inventory (as on Jan 1)]) + _priorSupply - _priorDemand
thanks for you quick response @d_gosbell !
Actually the data i am working with has lot more items. The inventories need to be calculated at item - month level.
Item | Month | Supply | Demand | On Hand inventory (as of Jan 1) | Starting inventory | Ending inventory |
A | 1/1/2019 | 16,727 | 7,396 | 32,944 | 32,944 | 42,275 |
A | 2/1/2019 | 0 | 8,700 | |||
A | 3/1/2019 | 0 | 10,300 | |||
A | 4/1/2019 | 37,000 | 10,140 | |||
A | 5/1/2019 | 18,500 | 11,220 | |||
A | 6/1/2019 | 0 | 9,940 | |||
A | 7/1/2019 | 37,000 | 8,380 | |||
A | 8/1/2019 | 0 | 16,936 | |||
A | 9/1/2019 | 0 | 7,100 | |||
A | 10/1/2019 | 0 | 9,560 | |||
B | 1/1/2019 | 6,000 | 12,971 | 152,549 | 152,549 | 139,578 |
B | 2/1/2019 | 0 | 13,689 | |||
B | 3/1/2019 | 0 | 24,886 | |||
B | 4/1/2019 | 17,039 | 24,776 | |||
B | 5/1/2019 | 0 | 14,055 | |||
B | 6/1/2019 | 0 | 13,769 | |||
B | 7/1/2019 | 0 | 23,921 | |||
B | 8/1/2019 | 0 | 1,750 | |||
B | 9/1/2019 | 0 | 1,750 | |||
B | 10/1/2019 | 18,500 | 1,750 |
I tried the formula you suggested but its not giving me the desired result : the result of the formula On Hand invenotry * # records in the table as value for each row (in my earlier example, i had 10 rows and 32,944 was starting inventory for Jan. the result of the formula was 329,440 for every record)
I'm not sure how you could get #rows times the starting inventory. However there are some issues with the previous expressions I post, but I think the general approach is still the correct one.
These two measures appear to do the trick
Starting Inventory2 = var _priorMonths = FILTER( ALL(Table1[Month]), Table1[Month] < MAX(Table1[Month] )) var _priorAndCurrentMonths = FILTER( ALL(Table1[Month]), Table1[Month] <= MAX(Table1[Month] )) RETURN CALCULATE(SUM(Table1[On Hand inventory (as of Jan 1)]),_priorAndCurrentMonths) + CALCULATE(SUM( Table1[Supply]) -SUM( Table1[Demand]) , _priorMonths )
Ending Inventory2 = var _priorMonths = FILTER( ALL(Table1[Month]), Table1[Month] <= MAX(Table1[Month] )) RETURN CALCULATE(SUM(Table1[On Hand inventory (as of Jan 1)]) + SUM( Table1[Supply]) -SUM( Table1[Demand]) , _priorMonths )
thank you so much @d_gosbell ! this is really helpful.
Apologies for the delayed response... since i am new to Power BI, it was not very apparent to me what the formula you suggested was evaluating but after doing some research and playing aroung with Vars, i now understand the logic.
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |