filter? Cumulative subtraction doesn't work

Hi ohnothimagain,

Accoridng to my knwoledge (which is still limited) there is a solution for your problem.

Because you want run cumulative sum for [Qty] column basing on [PN] column I think that it will be not enough to use date column during this agregation. The Measure 1 code (part of it was used in the previous example), in my opinion will result with the same cumulative result for the same PN value and date value (and different WBS values), check Table 1 . 

    Measure 1 = CALCULATE(
        SUM([Qty]), 
        FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date] <= CurrentDate)
    )​

Table 1.

I think that the remedy for this issue is using column that will somehow rank each row basing on date value nad WBS value. Then when table is properly sorted you will get desireble result. 

First step is to create Rank calculated column, which code you will find below:

Rank = CONVERT('Sheet1'[M_Date],INTEGER) * 1000000 + 'Sheet1'[WBS]

Why am I multiplying date by 1 000 000? Your WBS numbers consist of 5 digits, so if I want to be sure that on 28/02/2024 product with WBS 11253 will have lower rank that the same product (same WBS) the day after (29/02/2024) I need to multiply the date by number higher than 99 999. If you would have WBS number built from six digits then I would need to multiply by 10 000 000. 

Thanks to this column whe can built cumulative aggregation basing not only on date but also on WBS number (If I understood your requirements well it is something that you want to achive). 

Now we can built the measure that will acumulate QTY but will use STK only once per PN number. Here it goes: 

Cumulative Result STK - QTY = 

VAR _UniquePN = VALUES('Sheet1'[PN]) -- return unique PN values in the filter context
VAR _MaxRank = MAX('Sheet1'[Rank]) -- return maxmimum rank in the filter context
VAR _STK = SUMX(
                        _UniquePN,
                        CALCULATE(
                                    MAX('Sheet1'[STK]),
                                    REMOVEFILTERS('Sheet1'[WBS],'Sheet1'[Customer])
           ) -- for single row returns STK for single row, for collapsed date sum of STK per 
                PN (counted only once)

VAR _CumulativeQTY =   SUMX(
                        _UniquePN,
                        CALCULATE(
                                    SUM('Sheet1'[Qty]),
                                    'Sheet1'[Rank] <= _MaxRank,                                 
                              REMOVEFILTERS('Sheet1'[WBS],'Sheet1'[Customer],'Sheet1'[M_Date])
                        )
                ) - returns the cumulative QTY 
VAR _Result =  IF(_STK - _CumulativeQTY >=0, BLANK(), _STK - _CumulativeQTY)                   

RETURN
_Result

The only drawback of this solution is the fact that column [PN] needs to be before column [WBS], I hope it doesn't disqualify this solution. 

Please check how does it look like in my file (while expanded and collapsed). 

The only thing I am not sure, whether STK value should be acumulated across diferrent dates or it is unique per date (as in my solution)?

Please let me know what you think about it and if it fits your needs!

Cheers!

Jan