sum only if the value attends to a condition

[ClaudioF]

Hi everyone!

I am have a big trouble with finding the logic and the way to apply to dax;

I need to cumulatively sum values of each item for each order num, following the order, always giving the priority to the earlier order num, but i need the cumulative sum of each item to not sum some values that are bigger than the available stock i have got; i will explain better in an example:

using the table:

Order num	item	quantity	date
111	aaa	20	01/01/2025
111	bbb	10	01/01/2025
222	aaa	10	01/02/2025
333	aaa	20	01/03/2025
333	bbb	2	01/03/2025
444	bbb	30	01/03/2025
555	bbb	5	01/03/2025

If i focus only on the orders 333, 444 and 555; considering that my Table stock contains item bbb (20 quantity) and item aaa (20 quantity); if i apply the logic my first item is aaa in order 333, the cumulative sum will be now 20, wich is <= to my stock, then the status of this item in this order will be "available", in the order 333 item bbb, the sum of the item bbb will be 2, and it is <= to my stock, the result will be "available", in the order number 444, the sum of bbb will be now 32, wich is bigger than my stock, the result will be "out" and the sum will be at the end 2 again (i will not num if this is bigger than the stock), to the order number 555, the sum will be now 7, and the result will be "available". 

I don't know if this logic is the best way to try this, but, i cannot find out anything else; 

I would like to implement this logic to my dax code:

Measure =

VAR _date = CALCULATE(MAX('Table date'[date]), ALLSELECTED('Table date'))

-- available stock

VAR _stock =

    CALCULATE(

        SUM('Table stock'[quantity]),

        FILTER(

            ALL('Table stock'),

            'Table stock'[item] = MAX('Table orders'[Item])

        )

    )

-- Cumulative sum

VAR _runningTotal =

    CALCULATE(

        SUM('Table orders'[quantity]),

        FILTER(

            ALLSELECTED('Table orders'),

            [Item] = MAX('Table orders'[Item]) &&

            [date] <= MAX('Table orders'[date]) &&

            [Order num] <= MAX('Table orders'[Order num]) -- Considera o número do pedido

        )

    )

-- Status

VAR _status =

    IF(_runningTotal <= _stock, "available", "out")

RETURN

    IF(ISBLANK(_runningTotal), BLANK(), _status)

Can anyone pleeease help me? thats consuming my whole time..

Thankyou

[Anonymous]

Hi @ClaudioF ,

Any update on this? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

HI @ClaudioF,

You can try to use following measure formula and it will dynamic calculate result based on current filter effects:

Status = 
VAR stock =
    CALCULATE ( SUM ( T1[quantity] ), ALLSELECTED ( T1 ), VALUES ( T1[item] ) )
VAR rolling =
    CALCULATE (
        SUM ( T1[quantity] ),
        FILTER (
            ALLSELECTED ( T1 ),
            [date] <= MAX ( T1[date] )
                && [Order num] <= MAX ( T1[Order num] )
        ),
        VALUES ( T1[item] )
    )
RETURN
    IF ( stock <> BLANK (), IF ( stock <= rolling, "available", "out" ) )

Regards,

Xiaoxin Sheng