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 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-23Dec-23Jan-24Feb-24
Current stock500   
Forecast Sales - 100 - 100 - 100 - 100
Open Orders + 50 +50 + 50 + 50
     
Current month Closing stock450   
     
MEASURE: Opening Stock
= (Opening stock value from prev Month) + Open Orders - Forecast Sales		 450400350

 

Many thanks in advance

 

Need Help

2 Replies

  • 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.