Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
effertz12
Helper I
Helper I

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

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. 

effertz12_1-1675365847508.png

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? 


 

4 REPLIES 4
FreemanZ
Super User
Super User

hi @effertz12 

You can add a calculated column like:

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

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. 

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

Measure =
SUMX(
    FILTER(
        TableName,
        TableName[X]>0
    ),
     TableName[Y]
)
effertz12
Helper I
Helper I

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.