Return a calculated Value from a conditional and filtered maxx

[MrAnderson72]

Hello,

I have the table below and the following situation, please help me!

• The table is extracted from the DB (except for the last column, which is the expected result)
• I have a process where the Total is the number of non conformities (NCs) found into an Assessment
• The following Assessment are linked with the original one by the column "ID" (same number)
• I need to have a column with the number of NC reduced from last assessment (Initial Assessments are always zero)
• Basically, current line "Total" minus last assessment "Total" (same ID and highest SubID # from all the Sub IDs that are lower from the current one)
• The "Sub ID" is sequential but based on all the rows.

I tried to use MAXX with Filter, but with no success. Please, show me the way for solving this!!!

				(Expected Result)
ID	Sub ID	Total	Assessment	Reduction
1	1	6	Initial	0
1	5	5	Post	1
1	12	4	Post	1
1	17	2	Post	2
2	2	8	Initial	0
2	6	7	Post	1
2	18	2	Post	5
3	8	5	Initial	0
3	15	2	Post	3

[amitchandak · Accepted solution]

@MrAnderson72 , Create a new column

new column =
var _min = maxx(filter(Table, [ID] =earlier([ID]) && [Sub ID] < earlier([Sub ID])),[Sub ID])
var _2 =maxx(filter(Table, [ID] =earlier([ID]) && [Sub ID] =_min),[Total])
return
if(isblank(_2), 0,_2-[Total])