topic Re: EARLIER not working inside filter of virtual table in DAX Commands and Tips

EARLIER not working inside filter of virtual table

Anonymous — Fri, 18 Mar 2022 17:26:10 GMT

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!

Re: EARLIER not working inside filter of virtual table

tamerj1 — Fri, 18 Mar 2022 18:56:16 GMT

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.

Re: EARLIER not working inside filter of virtual table

Anonymous — Fri, 18 Mar 2022 20:53:46 GMT

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"

Re: EARLIER not working inside filter of virtual table

tamerj1 — Sat, 19 Mar 2022 02:48:34 GMT

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 ) )

Re: EARLIER not working inside filter of virtual table

Anonymous — Sat, 19 Mar 2022 13:35:18 GMT

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

Re: EARLIER not working inside filter of virtual table

tamerj1 — Sat, 19 Mar 2022 15:49:27 GMT

Thank you Anonymous

can you please explain what is the expected result?

Re: EARLIER not working inside filter of virtual table

tamerj1 — Sun, 20 Mar 2022 12:41:32 GMT

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 ) )

Re: EARLIER not working inside filter of virtual table

Anonymous — Mon, 21 Mar 2022 11:29:55 GMT

That actually worked! Thank you!