Create opening and closing stock for each month for each material

[willson98]

Hi everyone,

I am still relatively new to Power BI, and I really need help in creating a column (or measure) to define the opening stock and closing stock of each material for each month. Attached is a screenshot of part of the query table that I have developed so far. 

Thanks!

[Anonymous]

Whether you create a column or measure will depend how you want to use the result.  Here are some quick measures i've written:

Pallets In = SUM('YourTable'[Pallets Issued In])

Pallets Out = SUM('YourTable'[Pallets Issued Out])

openingStock = 
VAR filterEndDate = LASTDATE('YourTable'[Date])
VAR monthStart = DATE(YEAR(filterEndDate), MONTH(filterEndDate), 1)
RETURN

CALCULATE(
	[Pallets In] - [Pallets Out],
	ALLEXCEPT('YourTable', 'YourTable'[Material Code]),
	'YourTable'[Date] < monthStart
)

closingStock = 
VAR filterEndDate = LASTDATE('YourTable'[Date])
VAR monthEnd = IF(
    MONTH(filterEndDate) = 12,
    DATE(YEAR(filterEndDate) + 1, 1, 1),
    DATE(YEAR(filterEndDate), MONTH(filterEndDate) + 1, 1)
)
RETURN

CALCULATE(
	[Pallets In] - [Pallets Out],
	ALLEXCEPT('YourTable', 'YourTable'[Material Code]),
	'YourTable'[Date] < monthEnd
)

Here is a quick snap of what it looked like when i hand copied some of your data and used it with these measures:

[willson98 · Accepted solution]

Hi @Anonymous, thank you for the response. Now I am trying to summarize these data into a visual. These data are forecasted data, and what I am trying to achieve is to show if the closing stock each coming month exceeds the maximum capacity of the warehouse. However, when I put in the measure into the visualization, it gives me strange huge number, like total for closing stock in January is 700+, which is impossible. You can refer to the attached images below. Is there anything wrong with my method? Thanks!

[Anonymous]

I've done a quick test with the dummy data I had and there doesn't appear to be any issue from what I can see in the basic method.  There must be another element at play causing your strange numbers.

I'd recommend doing an export of the Visual.  You can do this by hoving over the Graph visual, finding the ... menu and selecting export.  Get that data into Excel and have a look at it.  You'll want the underlying data.

[willson98]

@Anonymous Thanks for the trick! I've figured out what causes the strangely huge numbers. However, now I have the actual data for the opening stock in January 2018, as shown below. How can I integrate this actual data into the measures? Apologize for not including the actual data earlier, and thanks a lot for the previous help!

[Anonymous]

My Suggestion would be to convert your "Opening Stock in January 2018" into a "Pallets Issued In" value of a date before your reporting.  That way the measure simply picks it up as normal.  Because of how the measures work the date of these records could be filtered out in your reporting so they don't show in a stange manner, if thats important.