Forum Discussion

learner03's avatar
learner03
Icon for Post Partisan rankPost Partisan
4 years ago
Solved

Estimated Delivery date based on cumulative count

I have 2 tables- 1) Open Purchase Order- this table shows when are the items arriving in the warehouse based on ETA W/H date.  Date Document Number Shipment Header Item Code ETA W/H Quantit...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    learner03 OK, I simplified the equation and was able to eliminate the GENERATESERIES. Not sure why I didn't do it this way the first time. Try this one:

    FIFO 2 = 
    VAR __DaysToFulfill = 10
    VAR __ItemCode = [Item Code]
    VAR __Date = [Date]
    VAR __QtyToFulfill = SUMX(FILTER('BackOrders',[Item Code] = __ItemCode && [Date] <= __Date),[Backorder Qty])
    VAR __Table = FILTER('OpenPurchaseOrders',[Item Code] = __ItemCode)
    VAR __Table1 = 
        ADDCOLUMNS(
            ADDCOLUMNS(__Table,"__tqty",SUMX(FILTER(__Table,[ETA W/H] <= EARLIER([ETA W/H])),[Quantity])),
            "__LoopCounter",[__tqty] - __QtyToFulfill
        )
    VAR __TargetDate = MINX(FILTER(__Table1, [__LoopCounter] >= 0),[ETA W/H])
    VAR __FulfillmentDate = IF(__TargetDate = BLANK(),BLANK(),__TargetDate+__DaysToFulfill) 
RETURN
    __FulfillmentDate

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

learner03 I believe you want something like the following assuming FIFO for the back oders. PBIX is attached below signature, you can ignore Table2 and TableA, that was somebody else's problem. Basically, a standard DAX while loop.

Column = 
    VAR __DaysToFulfill = 10
    VAR __ItemCode = [Item Code]
    VAR __Date = [Date]
    VAR __QtyToFulfill = SUMX(FILTER('BackOrders',[Item Code] = __ItemCode && [Date] <= __Date),[Backorder Qty])
    VAR __Table = 
        ADDCOLUMNS(
            SELECTCOLUMNS(
                FILTER(ALL('OpenPurchaseOrders'),[Item Code] = __ItemCode),
                "__date",'OpenPurchaseOrders'[ETA W/H],
                "__qty",'OpenPurchaseOrders'[Quantity]
            ),
            "__LoopCounter",[__qty] - __QtyToFulfill
        )
    VAR __TargetDate = MINX(FILTER(__Table, [__LoopCounter] >= 0),[__date])
    VAR __FulfillmentDate = IF(__TargetDate = BLANK(),BLANK(),__TargetDate+__DaysToFulfill) 
RETURN
    __FulfillmentDate

 

learner03's avatar
learner03
Icon for Post Partisan rankPost Partisan
4 years ago

Greg_Deckler https://drive.google.com/file/d/1iLS6xW1GyQUB0SFk7QR6413YwKYcEAgl/view?usp=sharing 

I used your formula in the attached pbix and I saw that there are few gaps in the new column that calculated delivery dates.

For example if I see Item Code "9013" in both the tables. The place where it is calculating till estimated date 15/2 is working correctly, after this it is going blank.

and in Open Purchase order table, I can see that there is more stock coming and it can fulfil the back order amount-

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    learner03 The blanks come in when there is not enough product coming in from purchase orders to fulfill the request. Therefore, no estimated delivery date. I will check the calculations to see where things are coming from.