Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
02-02-2022 18:49 PM - last edited 02-03-2022 05:09 AM
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