DAX code to selectively pull forward quantities from future months to meet MOQ

Question

For context, it is about ordering stock from multiple factories, where each has its own Minimum Order Quantity (MOQ) by Item Number. Each order may be split to go to different sites, as long as the total MOQ is met. I only need to calculate the order requirements for one selected month, which I can manipulate using a slicer.

I have an existing DAX model with four tables. I will number the Tables and then list the important columns under each, along with the data type in brackets, and will indicate relationships from the main fact table next to the applicable column name.

1. Dimension:
-Company Code (text)

-Inventory Site Code (text)

-Item Number (text)

-Variant (text)

-Multiple Quantity (integer)

-DimKey (text)

2. Vendors:

-Vendor (text)

-MOQ by Item (integer)

-VendorKey (text)

3. Calendar:

-Yearmonth (integer)

-YMIndex (integer)

4. Summary:

-PO/RB (text)

-Yearmonth (integer), with a relationship to the Yearmonth column in the Calendar table

-DimKey (text), with a relationship to the DimKey column in the Dimension table

-VendorKey (text), with a relationship to the Vendors table

-Qty (integer)

I then have the following existing measure: [Qty] = CALCULATE(SUM(Summary[Qty]))

I need help with writing a new measure that will calculate how much of the Qty I need to bring into the current month from future Yearmonth values for a particular Item Number but across all the other column values in the Dimension table (i.e. ALLEXCEPT Dimension[Item Number]), to the extent that I can make up a certain MOQ for an item - let's say that value is 500. So in the example above, I need to pull forward requirements out to 202403 to achieve the MOQ value.

There's a further complication, in that I want to pull forward only as much as I need to achieve the 500 target, provided that all the quantities I order are divisible by the Multiple Quantity column in the Dimension table. That is, if pulling forward requirements out to 202402 gave me 450 but adding 202403 gave me 600, I need to trim down the total number I'm pulling forward in a representative cut across all the variants from the 202403 value to get the total as close to 500 as possible, while meeting MOQ and each line needs to be divisible by the Multiple Quantity for the DimKey being evaluated.

andredelange · Answer

Sample data from 'Calendar' table:YearmonthYMIndex20231212024012202402320240342024045202405620240672024078&nbsp;Sample data in 'Vendors' table:VendorMOQ by ItemVendorKeyVendor 1500V1Vendor 2200V2&nbsp;Sample data in 'Summary' table:DimKeyYearmonthQtyVendorKeySite1AAAT20240216V1Site1AAAT20240316V1Site1AAAT2024048V1Site1AAAT20240516V1Site1AAAT20240616V1Site1AAAT2024078V1Site1AAAW20240232V1Site1AAAW20240324V1Site1AAAW20240432V1Site1AAAW20240532V1Site1AAAW20240624V1Site1AAAW20240716V1Site1AAASQ2023128V1Site1AAASQ20240124V1Site1AAASQ20240232V1Site1AAASQ20240340V1Site1AAASQ20240432V1Site1AAASQ20240540V1Site1AAASQ20240632V1Site1AAASQ20240724V1Site1AAAL2023128V1Site1AAAL20240124V1Site1AAAL20240224V1Site1AAAL20240332V1Site1AAAL20240432V1Site1AAAL20240532V1Site1AAAL20240624V1Site1AAAL20240724V1Site1AAALW20240116V1Site1AAALW20240216V1Site1AAALW20240324V1Site1AAALW20240424V1Site1AAALW20240524V1Site1AAALW20240616V1Site1AAALW2024078V1Site1AAAPR2024068V1Site1AAAG20240116V1Site1AAAG20240216V1Site1AAAG20240316V1Site1AAAG20240416V1Site1AAAG20240524V1Site1AAAG20240616V1Site1AAAG2024078V1Site1AAARK2024058V1Site1AAARK2024068V1Site1AAAZ20240216V1Site1AAAZ20240324V1Site1AAAZ20240416V1Site1AAAZ20240524V1Site1AAAZ20240616V1Site1AAAZ2024078V1Site1AAAJ2024078V1Site1AAAFW2024038V1Site1AAAFW20240416V1Site1AAAFW20240516V1Site1AAAFW2024068V1Site1AAAFW2024078V1Site1AAAK2024038V1Site1AAAK2024058V1Site1AAAK2024078V1Site1AAAMB2024018V1Site1AAAMB2024028V1Site1AAAMB20240316V1Site1AAAMB2024048V1Site1AAAMB20240516V1Site1AAAMB20240616V1Site1AAAMB2024078V1Site1AAAEQ2024018V1Site1AAAEQ2024028V1Site1AAAEQ2024048V1Site1AAAEQ2024058V1Site1AAAEQ2024068V1Site2AAAT2024078V1Site2AAAWZ2024058V1Site2AAAWZ2024078V1Site2AAAW2024058V1Site2AAAW20240716V1Site2AAAD2024068V1Site2AAAD20240724V1Site2AAASQ20240516V1Site2AAASQ2024068V1Site2AAASQ20240724V1Site2AAAXE2024058V1Site2AAAXE20240616V1Site2AAAXE20240716V1Site2AAAL2024058V1Site2AAAL2024068V1Site2AAAL20240724V1Site2AAAC2024058V1Site2AAAC2024068V1Site2AAAC20240716V1Site2AAALW2024058V1Site2AAALW2024068V1Site2AAALW20240724V1Site2AAAPR20240516V1Site2AAAPR2024068V1Site2AAAPR20240716V1Site2AAAG2024058V1Site2AAAG20240616V1Site2AAAG20240724V1Site2AAARK2024028V1Site2AAARK2024038V1Site2AAARK20240516V1Site2AAARK20240616V1Site2AAARK20240716V1Site2AAAZ2024038V1Site2AAAZ20240516V1Site2AAAZ20240616V1Site2AAAZ20240724V1Site2AAAJ20240516V1Site2AAAJ20240616V1Site2AAAJ20240724V1Site2AAAFW2024058V1Site2AAAFW20240616V1Site2AAAFW20240724V1Site2AAAK20240716V1Site2AAAMB2024068V1Site2AAAMB20240716V1Site2AAAEQ2024078V1&nbsp;Sample data in 'Dimension' table:Item NumberInventory Site CodeVariantMultiple QuantityAAASite1T8AAASite1W8AAASite1SQ8AAASite1L8AAASite1LW8AAASite1PR8AAASite1G8AAASite1RK8AAASite1Z8AAASite1J8AAASite1FW8AAASite1K8AAASite1MB8AAASite1EQ8AAASite2T8AAASite2WZ8AAASite2W8AAASite2D8AAASite2SQ8AAASite2XE8AAASite2L8AAASite2C8AAASite2LW8AAASite2PR8AAASite2G8AAASite2RK8AAASite2Z8AAASite2J8AAASite2FW8AAASite2K8AAASite2MB8AAASite2EQ8

Forum Discussion

DAX code to selectively pull forward quantities from future months to meet MOQ

1 Reply

Related content

Days Forward Coverage (DFC) Measure

Turning Grams to Quantity

Recursive Carried/Brought Forward

Selected slicer

Carry Forward Values until Change

Yearmonth	YMIndex
202312	1
202401	2
202402	3
202403	4
202404	5
202405	6
202406	7
202407	8

DimKey	Yearmonth	Qty	VendorKey
Site1AAAT	202402	16	V1
Site1AAAT	202403	16	V1
Site1AAAT	202404	8	V1
Site1AAAT	202405	16	V1
Site1AAAT	202406	16	V1
Site1AAAT	202407	8	V1
Site1AAAW	202402	32	V1
Site1AAAW	202403	24	V1
Site1AAAW	202404	32	V1
Site1AAAW	202405	32	V1
Site1AAAW	202406	24	V1
Site1AAAW	202407	16	V1
Site1AAASQ	202312	8	V1
Site1AAASQ	202401	24	V1
Site1AAASQ	202402	32	V1
Site1AAASQ	202403	40	V1
Site1AAASQ	202404	32	V1
Site1AAASQ	202405	40	V1
Site1AAASQ	202406	32	V1
Site1AAASQ	202407	24	V1
Site1AAAL	202312	8	V1
Site1AAAL	202401	24	V1
Site1AAAL	202402	24	V1
Site1AAAL	202403	32	V1
Site1AAAL	202404	32	V1
Site1AAAL	202405	32	V1
Site1AAAL	202406	24	V1
Site1AAAL	202407	24	V1
Site1AAALW	202401	16	V1
Site1AAALW	202402	16	V1
Site1AAALW	202403	24	V1
Site1AAALW	202404	24	V1
Site1AAALW	202405	24	V1
Site1AAALW	202406	16	V1
Site1AAALW	202407	8	V1
Site1AAAPR	202406	8	V1
Site1AAAG	202401	16	V1
Site1AAAG	202402	16	V1
Site1AAAG	202403	16	V1
Site1AAAG	202404	16	V1
Site1AAAG	202405	24	V1
Site1AAAG	202406	16	V1
Site1AAAG	202407	8	V1
Site1AAARK	202405	8	V1
Site1AAARK	202406	8	V1
Site1AAAZ	202402	16	V1
Site1AAAZ	202403	24	V1
Site1AAAZ	202404	16	V1
Site1AAAZ	202405	24	V1
Site1AAAZ	202406	16	V1
Site1AAAZ	202407	8	V1
Site1AAAJ	202407	8	V1
Site1AAAFW	202403	8	V1
Site1AAAFW	202404	16	V1
Site1AAAFW	202405	16	V1
Site1AAAFW	202406	8	V1
Site1AAAFW	202407	8	V1
Site1AAAK	202403	8	V1
Site1AAAK	202405	8	V1
Site1AAAK	202407	8	V1
Site1AAAMB	202401	8	V1
Site1AAAMB	202402	8	V1
Site1AAAMB	202403	16	V1
Site1AAAMB	202404	8	V1
Site1AAAMB	202405	16	V1
Site1AAAMB	202406	16	V1
Site1AAAMB	202407	8	V1
Site1AAAEQ	202401	8	V1
Site1AAAEQ	202402	8	V1
Site1AAAEQ	202404	8	V1
Site1AAAEQ	202405	8	V1
Site1AAAEQ	202406	8	V1
Site2AAAT	202407	8	V1
Site2AAAWZ	202405	8	V1
Site2AAAWZ	202407	8	V1
Site2AAAW	202405	8	V1
Site2AAAW	202407	16	V1
Site2AAAD	202406	8	V1
Site2AAAD	202407	24	V1
Site2AAASQ	202405	16	V1
Site2AAASQ	202406	8	V1
Site2AAASQ	202407	24	V1
Site2AAAXE	202405	8	V1
Site2AAAXE	202406	16	V1
Site2AAAXE	202407	16	V1
Site2AAAL	202405	8	V1
Site2AAAL	202406	8	V1
Site2AAAL	202407	24	V1
Site2AAAC	202405	8	V1
Site2AAAC	202406	8	V1
Site2AAAC	202407	16	V1
Site2AAALW	202405	8	V1
Site2AAALW	202406	8	V1
Site2AAALW	202407	24	V1
Site2AAAPR	202405	16	V1
Site2AAAPR	202406	8	V1
Site2AAAPR	202407	16	V1
Site2AAAG	202405	8	V1
Site2AAAG	202406	16	V1
Site2AAAG	202407	24	V1
Site2AAARK	202402	8	V1
Site2AAARK	202403	8	V1
Site2AAARK	202405	16	V1
Site2AAARK	202406	16	V1
Site2AAARK	202407	16	V1
Site2AAAZ	202403	8	V1
Site2AAAZ	202405	16	V1
Site2AAAZ	202406	16	V1
Site2AAAZ	202407	24	V1
Site2AAAJ	202405	16	V1
Site2AAAJ	202406	16	V1
Site2AAAJ	202407	24	V1
Site2AAAFW	202405	8	V1
Site2AAAFW	202406	16	V1
Site2AAAFW	202407	24	V1
Site2AAAK	202407	16	V1
Site2AAAMB	202406	8	V1
Site2AAAMB	202407	16	V1
Site2AAAEQ	202407	8	V1

Item Number	Inventory Site Code	Variant	Multiple Quantity
AAA	Site1	T	8
AAA	Site1	W	8
AAA	Site1	SQ	8
AAA	Site1	L	8
AAA	Site1	LW	8
AAA	Site1	PR	8
AAA	Site1	G	8
AAA	Site1	RK	8
AAA	Site1	Z	8
AAA	Site1	J	8
AAA	Site1	FW	8
AAA	Site1	K	8
AAA	Site1	MB	8
AAA	Site1	EQ	8
AAA	Site2	T	8
AAA	Site2	WZ	8
AAA	Site2	W	8
AAA	Site2	D	8
AAA	Site2	SQ	8
AAA	Site2	XE	8
AAA	Site2	L	8
AAA	Site2	C	8
AAA	Site2	LW	8
AAA	Site2	PR	8
AAA	Site2	G	8
AAA	Site2	RK	8
AAA	Site2	Z	8
AAA	Site2	J	8
AAA	Site2	FW	8
AAA	Site2	K	8
AAA	Site2	MB	8
AAA	Site2	EQ	8

Vendor	MOQ by Item	VendorKey
Vendor 1	500	V1
Vendor 2	200	V2