Delivery Dates

Greg_Deckler — Thu, 03 Feb 2022 13:09:26 GMT

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

topic Delivery Dates in Quick Measures Gallery

Delivery Dates