Error in a measure using Earlier

[Jorge1986]

Hi, 

I have a table called factVentas with lots of data, I need a dynamic report of paretto 80 20 by dates. The issue is that I have an error in making an accumulated sum within a measure.

Something like this.

where $SumValue and $Total1 are measured with the following formulas:

$SumValue = SUM (factVentas[VENTA NETA] )

$Total1 = SUMX(ALLSELECTED(factVentas), factVentas[VENTA NETA])

 Now, what is needed is to accumulate the value of $SumValue row by row, and I do it in the following way:

$VentasAcumuladas = SUMX(FILTER(factVentas, EARLIER(factVentas[$SumValue],1)<=factVentas[$SumValue]),factVentas[$SumValue])

The error says:  EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

This formula works well on a column, but I have this error as a measure. Any help in this case would be greatly appreciated. Greetings.

[v-yulgu-msft · Accepted solution]

Hi @Jorge1986,

EARLIER/EARLIEST function can be used in calculated column only , but not supported within a measure.

In your scenario, please first create a calculated column in 'facyVentas':

Rank col =
RANKX (
    ALL ( factVentas ),
    CALCULATE (
        SUM ( factVentas[VENTA NETA] ),
        ALLEXCEPT ( factVentas, factVentas[Category] )
    ),
    ,
    DESC,
    DENSE
)

Then, create a measure to return accumulated sum:

$VentasAcumuladas =
CALCULATE (
    SUM ( factVentas[VENTA NETA] ),
    FILTER (
        ALL ( factVentas ),
        factVentas[Rank col] <= MAX ( factVentas[Rank col] )
    )
)

Best regards,

Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @Jorge1986,

EARLIER/EARLIEST function can be used in calculated column only , but not supported within a measure.

In your scenario, please first create a calculated column in 'facyVentas':

Rank col =
RANKX (
    ALL ( factVentas ),
    CALCULATE (
        SUM ( factVentas[VENTA NETA] ),
        ALLEXCEPT ( factVentas, factVentas[Category] )
    ),
    ,
    DESC,
    DENSE
)

Then, create a measure to return accumulated sum:

$VentasAcumuladas =
CALCULATE (
    SUM ( factVentas[VENTA NETA] ),
    FILTER (
        ALL ( factVentas ),
        factVentas[Rank col] <= MAX ( factVentas[Rank col] )
    )
)

Best regards,

Yuliana Gu

[Jorge1986 · Accepted solution]

Dear @v-yulgu-msft, His contribution was vital to reach the solution, for my case I had to make small modifications and in that way make him work dynamically. Thank you very much, enormous support, enormous brain ... Greetings from Ecuador.