Opening Stock Position Measure Calculation

[nthomson]

Hello!
I am trying to create a measure that will show cumulative opening stock position in a matrix table but finding it challenging. 

The measure should sum the opening stock balance by taking the Opening stock  value from the previous month, then use values currently in 2 linked tables :

Add Purchase Order Qty 

Subtract Forecast Qty 

The starting value for the measure can be a measure 'Current month closing stock' which is a sum of current inventory, + Purchase Order Qty, - Forecast Qty for current month. 

	Nov-23	Dec-23	Jan-24	Feb-24
Current stock	500
Forecast Sales	- 100	- 100	- 100	- 100
Open Orders	+ 50	+50	+ 50	+ 50
Current month Closing stock	450
MEASURE: Opening Stock = (Opening stock value from prev Month) + Open Orders - Forecast Sales		450	400	350

Many thanks in advance

[danextian]

Hi @nthomson ,

Is that how your data is formatted - there's a separate column for each month? If so you need to unpivot your table first before doing any and create a date equivalent of your months (if they are a text string) before doing any calcuation. Your imported data should look like below:

And for your opening and closing stock measures:

Opening Stock = 
CALCULATE (
    SUM ( 'Table'[Values] ),
    FILTER (
        ALL ( 'Table'[Date], 'Table'[Period] ),
        'Table'[Date] < MAX ( 'Table'[Date] )
    )
)

Closing stock can be alternatively written as below since it is just cumulative sum of all inventory values.

Closing Stock = 
CALCULATE (
    SUM ( 'Table'[Values] ),
    FILTER (
        ALL ( 'Table'[Date], 'Table'[Period] ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

Please see attached pbix for your reference.

[Ashish_Mathur]

Hi,

I have solved similar problem in the 2 attached PBI files.  Please study them and apply those formulas to your dataset.

Hope this helps.