Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Last Column Value

Dears,

 

Have a good day.

 

I need to get the last value of a column, just read it without calculation as the value is a cumulative value.

 

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    Hi aserag,

     

    In your scenario, you want to achieve the value in the last row, right? For example, 54545 is what you want in column [Planned value] below.

    The first solution is that you can create an index and use the max index to find the value, click Query Editor->Add Column->Index Column.

    After applied, you can create a measure using DAX formula:

    last value = CALCULATE(MAX(Table1[Planned Value]), FILTER(Table1, Table1[Index] = MAX(Table1[Index])))

     

    The second solution is that you can simply use “Keep Bottom Rows” to achieve last value.

     

     

    Regards,

    Jimmy Tao

8 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi aserag,

     

    In your scenario, you want to achieve the value in the last row, right? For example, 54545 is what you want in column [Planned value] below.

    The first solution is that you can create an index and use the max index to find the value, click Query Editor->Add Column->Index Column.

    After applied, you can create a measure using DAX formula:

    last value = CALCULATE(MAX(Table1[Planned Value]), FILTER(Table1, Table1[Index] = MAX(Table1[Index])))

     

    The second solution is that you can simply use “Keep Bottom Rows” to achieve last value.

     

     

    Regards,

    Jimmy Tao

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi v-yuta-msft

       

      Thank you very much, I prefer the 1st solution. I tried it and it's working.

       

      Thanks again

       

      Regards

      Ahmed

      • saipawar's avatar
        saipawar
        Icon for Helper IV rankHelper IV
        6 years ago

        Hi v-yuta-msft ,

         

        I have read your solutions and have tried solution 1 partially. 

         

        Here's how my current table looks after indexing - 

         

        DESIRED VALUE = 51.7 M

        Current Calculation:

        Meetings this qtr = CALCULATE(MAX(DailyMtgUsage[NUMBER_OF_MEETINGS]), FILTER('Date','Date'[Max Quarter Rank] = MAX('Date'[Max Quarter Rank])))
         
        To Simplify in your format:
        Last value = CALCULATE(MAX(Table2[Planned Value]), FILTER(Table2, Table2[Index] = MAX(Table1[Index])))
         
        Here's how I created the index. This is a newly created measure to rank quarter start dates:
        Max Qtr Start Date = MAX( 'Date'[FISCAL_QUARTER_START_DATE])
        Max Quarter Rank = RANKX(ALL('Date'),[Max Qtr Start Date],,ASC,Dense)
         
        I also read somewhere that MAX takes only column values and not measures. Please suggest an alternative way to do this. I tried LASTNONBLANK function but that didn't quite work.
         
        Please help in retrieving the last value. I am not sure what is wrong in the dax code.
         
        Thanks
  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Will it always be the highest value?  If so you could use MAX([Planned Value (Cumulative)]

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you Phil_Seamark

       

      But in my case it is not, it depends on other values so it is not the highest vale.

      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Then do you have another column that is used to determine the order to get the last value?  Is it a Date column or transaction ID