Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Grab Date Where Short

Hello, 

 

I have a table full of work orders with quantity commited amounts and their respective WODueDate. I need to get the earliest date where our quantity on hand amount will no longer cover the order. 

So for example, I have 23 QtyOnHand for item code 00-201006 with these work orders. 

Work OrderWODueDateQTYNeededItemCode
HX56712/24/20221500-201006
HZ89701/15/2023600-201006
HB24602/25/2023700-201006
HD45603/05/2023800-201006

What I need returned is 02/25/2023 since there is only 23 on hand, 15+6 = 21, leaving two left which will not cover the 7 required for that WO.

Any help would be appreciated!

Thanks

 

Need Help

5 Replies

    • smpa01's avatar
      smpa01
      Icon for Community Champion rankCommunity Champion
      3 years ago

      CNENFRNL  this is brilliant; one more with new window function

      Measure = 
var qtAtHand = 23
RETURN 
CALCULATE (
    MAX ( 'Table'[WODueDate] ),
    FILTER (
        'Table',
        'Table'[WODueDate]
            = MAXX (
                TOPN (
                    1,
                    FILTER (
                        ALL ( 'Table' ),
                        CALCULATE (
                            SUM ( 'Table'[QTYNeeded] ),
                            WINDOW (
                                1,
                                ABS,
                                0,
                                REL,
                                 ALL ( 'Table'[WODueDate], 'Table'[ItemCode] ) ,
                                ORDERBY ( 'Table'[WODueDate], ASC ),
                                KEEP,
                                PARTITIONBY ( 'Table'[ItemCode] )
                            )
                        ) < qtAtHand
                    ),
                    'Table'[WODueDate], ASC
                ),
                'Table'[WODueDate]
            )
    )
)

       

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      How would one alter this measure to also only look at the work orders where their status is = Firm? 

      This is working for like 1/4 of my work orders but I forgot that im only needing to "apply" that QoH to ones that have that status.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Also I am getting some blank results from this measure. I have one item code that has a QoH of 0, and one single work order with 1 as the Qty and a date of 06/09/2023. This should be returning that 06/09/2023 but instead it is blank. I tried altering the measure to be like this:  

        NeedByDate2 =
        IF(
        MAXX(
            FILTER(
                WO2_WorkOrderMaterialDetail,
                VAR __DT = WO2_WorkOrderMaterialDetail[WODueDate_CC]
                RETURN
                    SUMX( FILTER( WO2_WorkOrderMaterialDetail, WO2_WorkOrderMaterialDetail[WODueDate_CC] < __DT ), WO2_WorkOrderMaterialDetail[QtyCommitted] ) < [TotalQuantity]
                    && SUMX( FILTER( WO2_WorkOrderMaterialDetail, WO2_WorkOrderMaterialDetail[WODueDate_CC] <= __DT ), WO2_WorkOrderMaterialDetail[QtyCommitted] ) >= [TotalQuantity]
                    ),
                    WO2_WorkOrderMaterialDetail[WODueDate_CC]
        )
        = NULL, CALCULATE(MIN(WO2_WorkOrderMaterialDetail[WODueDate_CC]), WO2_WorkOrderMaterialDetail[OrderStatus] = "F"), MIN(WO2_WorkOrderMaterialDetail[WODueDate_CC]))
        But it is saying that I cannot use NULL in this context. Any ideas?


        I was able to get it to work by using  BLANK() there instead of NULL. Now though its as if its not even doing the full measure calculation you gave me and instead just doing the "CALCULATE(MIN(WO2_WorkOrderMaterialDetail[WODueDate_CC]), WO2_WorkOrderMaterialDetail[OrderStatus] = "F")" measure I put for the "Result If true" in my "IF" statement for each item code.
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Im finding that all the ones that are blank are the ones whos quantity on hand is currently 0