Measure to calculate the sum of all the months previous without Neg Months included

[effertz12]

Hi, I have a formula that is used in a matrix that calculates the my (Open Orders + Ships) - FC qty. 

it was Set up as an iterating view in SQL that can no longer be used. 

the Formula for Open slots is (OpenOrder +Ships) - FCqty + IF(X>0, X, 0)

x is equal to the previous months open slots. 
is there a way to do this in a DAX formula? 

[FreemanZ]

hi @effertz12 

You can add a calculated column like:

Y =

[Open_Order_Qty] +[Ship_Qty] - [FC_Qty] + IF([X]>0, [X], 0)

[effertz12]

my data set is individual orders and ships on one table and forecast for those items in a sepereate table. I cant calulate at the table level because we have different groupings that i need to be able to choose between which would mean i would need a table for every grouping we choose.

var Shipqty =  

            CALCULATE(

                    SUM(SHIPORDER[qty_shipped])

                        ,FILTER(SHIPORDER, SHIPORDER[ORMonth] <= DATES && SHIPORDER[DataType] = "MTD Ship" ))  

var Orderqty =  

            CALCULATE(

                    SUM(SHIPORDER[qty_ordered])

                        ,FILTER(SHIPORDER,SHIPORDER[ORMonth] <= DATES  && SHIPORDER[DataType] = "Open Order"))

var Holdqty  =

            CALCULATE(

                    SUM(HOLDS[qty_ordered])

                        ,FILTER(HOLDS, HOLDS[ORmonth] <= DATES && HOLDS[WFMP] = "Hold" ))

var FCqty  =

            CALCULATE(

                    SUM(FC[Units])

                        ,FILTER(FC, FC[FCDate] <= DATES  ))
return 

 Shipqty + Orderqty - FCqty

this is the formula im currently using. 

[FreemanZ]

or you already have the Y column, but expecting something like:

Measure =

SUMX(

    FILTER(

        TableName,

        TableName[X]>0

    ),

     TableName[Y]

)

[effertz12]

my current formula evaluates OpenOrder+ships - FC for all the previous months up to the current month. Is there a way to not include months in which the OpenOrder+ships - FC formula goes negative? for example, in my picture above April would evaluate only the months of march and april but exclude febuary from the calc as it was negative in that month