filter? Cumulative subtraction doesn't work

[ohnothimagain]

Hello everyone 🙂  I'm working in production plannning control,hope anyone can save me...

I want to create a table that allocates inventory by each PN,but the cumulative calculation is incorrect...
->I need to keep Result's Column layout.but WBS context can't remove
How can I revise this?   Stock value in each row is duplicate.

■Table

■Measure

cml:=if(max('TEST_'[WBS])="",blank(),
if('TEST_'[stk_]-calculate(sum('TEST_'[Qty]),filter(allselected('Calendar'),'Calendar'[Date]<=max('Calendar'[Date])))<0,
'TEST_'[stk_]-calculate(sum('TEST_'[Qty]),filter(ALLSELECTED('Calendar'),'Calendar'[Date]<=max('Calendar'[Date]))),blank()
))

■Result

Thank you for your continuing support.
Best Regards,

[PowerBIHelper20 · Accepted solution]

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.

M_Date (DD/MM/YYYY)	WBS	PN	QTY	Cumulative QTY (Measure 1)	Cumulative QTY (Your expected outcome)
02/27/2024	11253	X-0002-000-1000	300	700	300
02/27/2024	29734	X-0002-000-1000	400	700	700

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

[DataNinja777]

Hi @ohnothimagain ,

It looks like you're trying to allocate inventory per PN while keeping the Result column layout intact, but the cumulative calculation isn't working correctly due to the WBS context. The main issue appears to be that stk_ is duplicating across rows, leading to incorrect stock distribution.

To address this, we need to ensure that stk_ is only counted once per PN, while still maintaining the WBS context. Additionally, we should adjust the cumulative calculation so that it correctly sums up quantities without double-counting.

bleep :=
VAR CurrentDate = MAX('Calendar'[Date])
VAR TotalStock = MAX('TEST_'[stk_])  -- Ensure stock is only counted once per PN
VAR UsedQty = 
    CALCULATE(
        SUM('TEST_'[Qty]), 
        FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date] <= CurrentDate)
    )
VAR RemainingStock = TotalStock - UsedQty

RETURN
IF(
    MAX('TEST_'[WBS]) = "",
    BLANK(),
    IF(RemainingStock < 0, RemainingStock, BLANK())
)

This approach ensures that stock is counted only once per PN by using MAX('TEST_'[stk_]), preventing duplication across rows. The use of ALLSELECTED('Calendar') maintains slicer context while allowing for cumulative summation over dates. Finally, the condition IF(RemainingStock < 0, RemainingStock, BLANK()) ensures that only relevant negative values are displayed.

If the stock is still incorrect and is summing up across different PNs incorrectly, replacing MAX('TEST_'[stk_]) with SUMX(VALUES('TEST_'[PN]), MAX('TEST_'[stk_])) might help to correctly distribute stock at the PN level.

Let me know if this resolves the issue or if further refinements are needed!

Best regards,

[ohnothimagain]

Thank you for your replying quickly!
I just tried two of your remedies ,but unfortunately the result was same.
I found Qty accumulation only works when [Date] field closed.

context is really complicated for me..
I don't deal with pbix file by now,sorry for inconvinient:X