EARLIER not working inside filter of virtual table

[Anonymous]

Hi! 

I'm trying to apply this to a column, but it's not returning the values i expected:

Disponível =

var tp =

TOPN(10,FILTER(Disponibilidade, Disponibilidade[Descricao] = 'Previsão de Entrega'[Grupo] && Disponibilidade[Data] >= 'Previsão de Entrega'[Sequencia Corrigida]), Disponibilidade[Data], ASC)

var temp =

ADDCOLUMNS(

tp,

"Soma", CALCULATE(SUMX(tp,[Carga Disponível]),FILTER(tp, [Data]<=EARLIER([Data]))))

RETURN CALCULATE(MINX(temp,[data]),FILTER(temp, [Soma] > 0))

So i applied this to a new table, replacing the column references with constant values to see what is going on:

Seems like the [Data] =< EARLIER([Data]) filter is not working in this context of virtual table. Any tips on how to make it work?

Thanks in advance!

[tamerj1]

Thank you @Anonymous 

can you please explain what is the expected result?

[tamerj1]

Hi @Anonymous 

you have nested iterators. I don't to which level of iteration you want to go back. But you can use EARLIEST to go back to the very first one. You can define inside EARLIER how many levels you need to go back. Alternatively you can store in a variable at the desired level. 

[Anonymous]

I tried passing 2 as an argument of earlier, but it returns "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

I just wanted the sum of "Carga Disponível" where the date is equal or less than the one in the line context of the virtual table "tp"

[tamerj1]

@Anonymous 

You cannot access the row context created by ADDCOLUMNS using EARLIER because you have CALCULATE which performs context transition. Therefore, you have to store it in variable outside CALCULATE 

I hope this works with you

Disponível =
VAR tp =
    TOPN (
        10,
        FILTER (
            Disponibilidade,
            Disponibilidade[Descricao] = 'Previsão de Entrega'[Grupo]
                && Disponibilidade[Data] >= 'Previsão de Entrega'[Sequencia Corrigida]
        ),
        Disponibilidade[Data], ASC
    )
VAR temp =
    ADDCOLUMNS (
        tp,
        "Soma",
            VAR CurrentTPDate = [Date]
            RETURN
                CALCULATE (
                    SUMX ( tp, [Carga Disponível] ),
                    FILTER ( tp, [Data] <= CurrentTPDate )
                )
    )
RETURN
    CALCULATE ( MINX ( temp, [data] ), FILTER ( temp, [Soma] > 0 ) )

[Anonymous]

Tthanks for exlpaining the context issue!

But i tried your code and it still returns the same results, unfortunatelly 

Here's how it looks on the test table

[tamerj1 · Accepted solution]

Hi @Anonymous 
try this

Disponível =
VAR tp =
    TOPN (
        10,
        FILTER (
            Disponibilidade,
            Disponibilidade[Descricao] = 'Previsão de Entrega'[Grupo]
                && Disponibilidade[Data] >= 'Previsão de Entrega'[Sequencia Corrigida]
        ),
        Disponibilidade[Data], ASC
    )
VAR temp =
    ADDCOLUMNS (
        tp,
        "Soma",
            VAR CurrentTPDate = [Date]
            RETURN
                SUMX ( FILTER ( tp, [Data] <= CurrentTPDate ), [Carga Disponível] )
    )
RETURN
    CALCULATE ( MINX ( temp, [data] ), FILTER ( temp, [Soma] > 0 ) )

[Anonymous]

That actually worked! Thank you!