Forum Discussion

Jorge1986's avatar
Jorge1986
Regular Visitor
8 years ago
Solved

Error in a measure using Earlier

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's avatar
    v-yulgu-msft
    8 years ago

    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's avatar
    Jorge1986
    8 years ago
    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.

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Jorge1986
      Regular Visitor
      8 years ago
      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.