Forum Discussion

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.

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

Community Support

8 years ago

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