SUMX is adding more than the parameters given in FILTER argument. What am I doing wrong?

[SSMalcontent]

I am working with a dataset in which I need to calculate a series of parameters into a "projection", one of which is the last known stock.

My formula to get this data into a calculated column is:

PROJECAOEstoqueInicial = SUMX(FILTER(PROJECAO,PROJECAO[Data]=MAXX(FILTER(PROJECAO,PROJECAO[CODPROD]=EARLIER(PROJECAO[CODPROD])&&PROJECAO[Data]<=EARLIER(PROJECAO[Data])&&PROJECAO[EstoqueInicial]<>BLANK()),PROJECAO[Data])&&PROJECAO[CODPROD]=EARLIER(PROJECAO[CODPROD])),PROJECAO[EstoqueInicial])

In theory, this should give me the last known stock before the current date, but it is instead adding all of the stock info regardless of date. I'm pretty sure is something easy to fix but I'm pretty stumped.

[Anonymous · Accepted solution]

Hi @SSMalcontent ,

Please have a try.

PROJECAOEstoqueInicial =
VAR _1 =
    MAXX (
        FILTER (
            PROJECAO,
            PROJECAO[CODPROD] = EARLIER ( PROJECAO[CODPROD] )
                && PROJECAO[Data] <= EARLIER ( PROJECAO[Data] )
                && PROJECAO[EstoqueInicial] <> BLANK ()
        ),
        PROJECAO[Data]
    )
RETURN
    SUMX (
        FILTER (
            PROJECAO,
            PROJECAO[Data] = _1
                && PROJECAO[CODPROD] = EARLIER ( PROJECAO[CODPROD] )
        ),
        PROJECAO[EstoqueInicial]
    )

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[SSMalcontent]

Heya, thanks a lot mate! Worked like a charm!

Can you explain to me why me original formula didn't though? For what it looks like, your formula just broke it down in two pieces, but used the same arguments and functions... something to do with context, maybe?