Calculated Column for Sum while comparing to another value

[Anonymous]

I'm looking to create a calculated column to sum a value (Quantity) from another table. Until the calculated amount (Quantity) is >= another value (total on hand). I'm not 100% certain what direction to head towards. I have attempted a couple of things but all directions just get me the grand total amounts of the matching records, shown at the bottom of the screen shot. Hopefully I explained what I am attempting to accomplish well enough. Any suggestions are greatly appreciated!

[Anonymous]

I'm following what you are doing. So Close! Question though. What do I do in the instance that I have more then one record for a given item id on a given day? That seems to be messing with the running total. I very much appreciate your effort to assist! 

Thanks!

Zach

[v-easonf-msft]

Hi， @Anonymous 

You can try to add an Index column after sorting the data in 'Transform data' (in descending order of ItemID and Transaction).

Then recalculate the running total based on this new Index column rather than 'Transaction Date'.

Best Regards,
Community Support Team _ Eason

[v-easonf-msft · Accepted solution]

Hi， @Anonymous 

Please try calculated columns as below:

Quantity running total = 
CALCULATE (
    SUM ( 'Table'[Quantity] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Transaction Date] >= EARLIER ( 'Table'[Transaction Date] )
    )
)

Extend Purchase price running total = 
CALCULATE (
    SUM ( 'Table'[Extend Purchase Price] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Transaction Date] >= EARLIER ( 'Table'[Transaction Date] )
    )
)

Result1 = 
CALCULATE (
    MIN ( 'Table'[Quantity running total] ),
    FILTER (
        'Table',
        'Table'[Quantity running total] >= 'Table 2'[Total of Hand]
            && 'Table'[Item ID] = 'Table 2'[Item ID]
    )
)

Result2 = 
CALCULATE (
    MAX ( 'Table'[Extend Purchase price running total] ),
    FILTER (
        'Table',
        'Table'[Quantity running total] = 'Table 2'[Result1]
            && 'Table'[Item ID] = 'Table 2'[Item ID]
    )
)

result:

Best Regards,
Community Support Team _ Eason