Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum most recent entries

Hi,

In one table I have many entries for the same NO_PAL. I added an Index column where the smaller number is the most recent entry. I want to sum "Total" of the last entry of each NO_PAL.

Thanks

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Anonymous ,

    For your requirement, you could create the below measures to achieve your desired output.

    minindex = CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[No_PAL]))
Measure =
SUMX (
    FILTER (
        'Table',
        'Table'[Index] = [minindex]
            && 'Table'[No_PAL] = MAX ( 'Table'[No_PAL] )
    ),
    SUM ( 'Table'[Total] )
)

    Here is the output.

    Best Regards,

    Cherry

     

5 Replies

  • EVEAdmin's avatar
    EVEAdmin
    Icon for Post Patron rankPost Patron
    7 years ago

    Can you use a filter with the TOP N option? In your case, N will be 1. 

    Alternately, you can probably drop the date field in a filter and again use only the TOP 1 record.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for your reply!

      I made this measure to calculate the sum of Total off all the min value for all NO_PAL but it's not what I'm looking for:

      SUMX(Table1;CALCULATE(MIN(Table1[Total]);ALLEXCEPT(Table1;Table1[NO_PAL])))

      I need the last value of all NO_PAL, wich is determined by the index column. I don't know how TopN function works correctly in this type of measure.

      Thanks

      • EVEAdmin's avatar
        EVEAdmin
        Icon for Post Patron rankPost Patron
        7 years ago
        Anonymous wrote:

        Thanks for your reply!

         

        I need the last value of all NO_PAL, wich is determined by the index column. I don't know how TopN function works correctly in this type of measure.

        Thanks

        From that table in your screen shot, what is the "last value of all NO_PAL, which is determined by the index column" ?
        Is it 1 ?