Select the last row with filter

[JhunePol]

Hello guys,

Hi @Suvrutt_Gurjar 

Can someone help me?

I have a form like this

The column [Jo#] was refer to the parent so that is not editable. Then I have an expression here

ANY(
SELECT(
Diecut Process Slip Sheet[Cumulative],
(
[_ROWNUMBER] = MAX(
SELECT(
Diecut Process Slip Sheet[_ROWNUMBER],
([_THISROW].[Jo#] = [Jo#]),[_THISROW].[Batch] = [Batch]
)
)
)
)
) 

When you see the 1st picture, there is a last virtual column named [Total Cumulative for this batch]. That is from the previous data based on the Jo#. Please see the highlighted from spreadsheet. 

Now i want to change the column named [Batch] example AA02.. Then the [Total Cumulative for this batch] will change into 3,000..

What can i do in my expression? I want to filter first by using Jo# then filter again using Batch to get the last value in the column named [Cumulative] which is i want to transfer the last value in the [Total Cumulative for this batch].

Thank you in advance! 

[Benny_E · Accepted solution]

To achieve your goal of filtering with Jo# as a first filter and then by Batch to get the last value from the [Cumulative] column before you display it in [Total Cumulative for this batch] virtual column, you can modify your expression with this updated expression:
ANY(
SELECT(
Diecut Process Slip Sheet[Cumulative],
AND(
([Jo#] = [_THISROW].[Jo#]),
([Batch] = [_THISROW].[Batch]),
[_ROWNUMBER] = MAX(
SELECT(
Diecut Process Slip Sheet[_ROWNUMBER],
AND(
([Jo#] = [_THISROW].[Jo#]),
([Batch] = [_THISROW].[Batch])
)
)
)
)
)
)
This will return the last cumulative value for the given Jo# and Batch, and it will display in the [Total Cumulative for this batch] virtual column. Hope that expression helps!

[JhunePol]

@Benny_E 

Oh I forgot to put expression AND(). Thank you 😊

[Benny_E]

All good! keep up the good work 😁