Accumulative function and sum not working

[Torbuzz]

Hi All,

Below is my data sample

I want to have result like this 

I use this formula that the accumulation didn't work

SisaStok =

Var TotInItem = SUM('Item'[In])

Var TotOutItem = SUM('Item'[Out])

Var TotAll = TotInItem - TotOutItem

Var Result = CALCULATE(IF(TotAll<0,0,TotAll),FILTER(ALLSELECTED(Kalender),Kalender[Date]<=MAX(Kalender[Date])))

RETURN

Result

Any support on this, highly appriciated.

[Torbuzz]

Now I change the formula to be like this :

SisaStok =

Var Result = CALCULATE([GrandTotal],FILTER(ALLSELECTED(Kalender),Kalender[Date]<=MAX(Kalender[Date])))

RETURN

IF(Result<0,0,Result)

The result of the February speaker item is not 20 and the sum value should be 200

What should I do? 

[Anonymous · Accepted solution]

Hi @Torbuzz ,

If you want to get result as your question, I think you need to sort your table by [Date] and [Name].

Measure:

GrandTotal = CALCULATE(SUM('Table'[In]) - SUM('Table'[Out]))

SisaStok =
VAR _Step1 =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[Index],
        Kalender[Year],
        Kalender[MonthSort],
        'Table'[Name],
        "GrandTotal", [GrandTotal],
        "Group",
            MAXX (
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    [GrandTotal] < 0
                        && 'Table'[Index] < EARLIER ( 'Table'[Index] )
                ),
                'Table'[Index]
            )
    )
VAR _Step2 =
    ADDCOLUMNS (
        _Step1,
        "RunningTotal",
            IF (
                [GrandTotal] < 0,
                0,
                SUMX (
                    FILTER (
                        _Step1,
                        [Group] = EARLIER ( [Group] )
                            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
                    ),
                    [GrandTotal]
                )
            )
    )
RETURN
    SUMX ( FILTER ( _Step2, [Index] IN VALUES ( 'Table'[Index] ) ), [RunningTotal] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Torbuzz]

Hi @Anonymous ,

Perfect, many thanks.