Forum Discussion

gan5133's avatar
gan5133
Regular Visitor
8 years ago
Solved

Credits Assignment to Prior Purchase Invoices

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_AcctIDDescriptionDateOfSaleINVOICE NUMPurchase QtyLinesTo CreditAssigned
10019055304_876TRANSDERM SCOP MULTPK PATCH 241/10/201895727227 
10019055304_876TRANSDERM SCOP MULTPK PATCH 241/8/201837246717 
10019055304_876TRANSDERM SCOP MULTPK PATCH 241/2/201834058827 
10019055304_876TRANSDERM SCOP MULTPK PATCH 2412/26/201724454227 
10019055304_876TRANSDERM SCOP MULTPK PATCH 2412/20/201756622317 
10019055304_876TRANSDERM SCOP MULTPK PATCH 2412/19/201730785317 
10019055304_876TRANSDERM SCOP MULTPK PATCH 2411/28/201736068017 
10019055304_876TRANSDERM SCOP MULTPK PATCH 2410/24/201749742717 
10019055304_876TRANSDERM SCOP MULTPK PATCH 249/29/201727803417 
10019055304_876TRANSDERM SCOP MULTPK PATCH 248/21/201701600017 
10019055304_876TRANSDERM SCOP MULTPK PATCH 247/31/201741549417 
10019055304_876TRANSDERM SCOP MULTPK PATCH 247/17/201776121217 
10019055304_876TRANSDERM SCOP MULTPK PATCH 246/21/201751081117 
10019055304_876TRANSDERM SCOP MULTPK PATCH 245/17/201750657617 
10019055304_876TRANSDERM SCOP MULTPK PATCH 244/29/201756060117 
10019055304_876TRANSDERM SCOP MULTPK PATCH 244/21/201713127617 
10019055304_876TRANSDERM SCOP MULTPK PATCH 244/19/201766611017 
10019055304_876TRANSDERM SCOP MULTPK PATCH 244/14/201793732317 
10019055304_876TRANSDERM SCOP MULTPK PATCH 244/10/201799001717 
   Total22  
  • 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" )

1 Reply

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion

    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" )