Delivery Dates 02-02-2022 18:49 PM - last edited 02-03-2022 05:09 AM

[Greg_Deckler]

Implementation of a DAX "while" loop to determine earliest possible delivery date for back orders. Included in the PBIX are FIFO, LIFO, Optimized for Most Orders and Optimized for Most Quantity.

FIFO Measure = 
    VAR __DaysToFulfill = 10
    VAR __ItemCode = MAX('BackOrders'[Item Code])
    VAR __Date = MAX('BackOrders'[Date])
    VAR __QtyToFulfill = SUMX(FILTER(ALL('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

Here is the LIFO version:

LIFO Measure = 
    VAR __DaysToFulfill = 10
    VAR __ItemCode = MAX('BackOrders'[Item Code])
    VAR __Date = MAX('BackOrders'[Date])
    VAR __QtyToFulfill = SUMX(FILTER(ALL('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

And, bonus! Here are the MSHGQM custom measure definitions for both FIFO and LIFO! What? You're not using MSHGQM? You are missing out!!

gdeckler/MicrosoftHatesGregsQuickMeasures: Microsoft Hates Greg (github.com)

Microsoft Hates Greg - DAX While Loops - YouTube

Delivery Days FIFO = 
VAR __DaysToFulfill = __PARAM5__
    VAR __ItemCode = MAX(__COLUMN__)
    VAR __Date = MAX(__COLUMN1__)
    VAR __QtyToFulfill = SUMX(FILTER(ALL('__TABLENAME__'),[__COLUMNNAME__] = __ItemCode && [__COLUMN1NAME__] <= __Date),[__COLUMN2NAME__])
    VAR __Table = FILTER('__TABLE3NAME__',[__COLUMN3NAME__] = __ItemCode)
    VAR __Table1 = 
        ADDCOLUMNS(
            ADDCOLUMNS(__Table,"__tqty",SUMX(FILTER(__Table,[__COLUMN4NAME__] <= EARLIER([__COLUMN4NAME__])),[__COLUMN5NAME__])),
            "__LoopCounter",[__tqty] - __QtyToFulfill
        )
    VAR __TargetDate = MINX(FILTER(__Table1, [__LoopCounter] >= 0),[__COLUMN4NAME__])
    VAR __FulfillmentDate = IF(__TargetDate = BLANK(),BLANK(),__TargetDate+__DaysToFulfill) 
RETURN
    __FulfillmentDate

And the LIFO version:

Delivery Days LIFO = 
VAR __DaysToFulfill = __PARAM5__
    VAR __ItemCode = MAX(__COLUMN__)
    VAR __Date = MAX(__COLUMN1__)
    VAR __QtyToFulfill = SUMX(FILTER(ALL('__TABLENAME__'),[__COLUMNNAME__] = __ItemCode && [__COLUMN1NAME__] >= __Date),[__COLUMN2NAME__])
    VAR __Table = FILTER('__TABLE3NAME__',[__COLUMN3NAME__] = __ItemCode)
    VAR __Table1 = 
        ADDCOLUMNS(
            ADDCOLUMNS(__Table,"__tqty",SUMX(FILTER(__Table,[__COLUMN4NAME__] <= EARLIER([__COLUMN4NAME__])),[__COLUMN5NAME__])),
            "__LoopCounter",[__tqty] - __QtyToFulfill
        )
    VAR __TargetDate = MINX(FILTER(__Table1, [__LoopCounter] >= 0),[__COLUMN4NAME__])
    VAR __FulfillmentDate = IF(__TargetDate = BLANK(),BLANK(),__TargetDate+__DaysToFulfill) 
RETURN
    __FulfillmentDate

eyJrIjoiODRlZGY3N2QtODZlNC00M2Y1LTkzNWMtOGIxMjBjYTA1ODM1IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9