Forum Discussion

nthomson's avatar
nthomson
Frequent Visitor
2 years ago

Opening Stock Position Measure Calculation

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 ...
Need Help
danextian's avatar
danextian
Super User
2 years ago

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.