Credits Assignment to Prior Purchase Invoices

[gan5133]

Using Power Pivot, I've built a model that lists prior invoices, newest to oldest, and need to write a DAX formula that scans the purchase quantity and identifies the rows (quantity) that add up to the LinesToCredit quantity.  By way of example, the model results in 13,769 rows with NDC and account number concatanates, purchase events (invoice num) and purchase quantity for each sale.  I need DAX formula that scans the purchase column, identifies the rows that add up to the number quantity in lines to credit (repeated on each purchase row).  In the example below, how do I identify how many rows it takes to sum to the seven (7) LinesToCredit to in Purchase Quantity?  In this example, there are 19 purchase events (rows) totaling 22.  It takes 4 rows to equal the credit quantity of 7.  Once I've identified the rows to 'assign', the remaining rows can be removed.  I have been manually scanning the Purchase Qty rows and making the assignment and want to automate this action.  gan5133

NDC_AcctID	Description	DateOfSale	INVOICE NUM	Purchase Qty	LinesTo Credit	Assigned
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	1/10/2018	957272	2	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	1/8/2018	372467	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	1/2/2018	340588	2	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	12/26/2017	244542	2	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	12/20/2017	566223	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	12/19/2017	307853	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	11/28/2017	360680	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	10/24/2017	497427	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	9/29/2017	278034	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	8/21/2017	016000	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	7/31/2017	415494	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	7/17/2017	761212	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	6/21/2017	510811	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	5/17/2017	506576	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	4/29/2017	560601	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	4/21/2017	131276	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	4/19/2017	666110	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	4/14/2017	937323	1	7
10019055304_876	TRANSDERM SCOP MULTPK PATCH 24	4/10/2017	990017	1	7
			Total	22

[Zubair_Muhammad · Accepted solution]

Hi @gan5133

Try this Calculated Column

Assigned =
VAR CumulativeQty =
    CALCULATE (
        SUM ( Table1[Purchase Qty] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[NDC_AcctID] ),
            Table1[DateOfSale] >= EARLIER ( [DateOfSale] )
        )
    )
RETURN
    IF ( CumulativeQty <= 7, "Assign", "Remove" )