Latest value after filters

[alexburn]

Hi guys,

Here is an example of my raw data:

Job______	Period___	Date______________	Progress____
11111	N1	01 May 2021	50
11111	N2	01 June 2021	60
11111		01 July 2021	0
22222	N1	01 May 2021	80
22222	N2	01 June 2021	90
22222	N3	01 July 2021	90
22222		01 August 2021	0
33333		01 June 2021	0
33333	N2	01 July 2021	20
33333		01 August 2021	0

What I need to end up with is this:

Job______	Period___	Date______________	Progress____	LatestProgress__
11111	N1	01 May 2021	50	60
11111	N2	01 June 2021	60	60
11111		01 July 2021	0	60
22222	N1	01 May 2021	80	90
22222	N2	01 June 2021	90	90
22222	N3	01 July 2021	90	90
22222		01 August 2021	0	90
33333		01 June 2021	0	20
33333	N2	01 July 2021	20	20
33333		01 August 2021	0	20

In other words...

Find the "progress" against the latest "date" where "period" is not blank.

I've had some success using calculations that return a value for the latest date, but having trouble filtering so that I'm only finding the latest date where the period is not blank.

Any help would be appreciated.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column fomula works

=LOOKUPVALUE(Data[Progress____],Data[Date______________],CALCULATE(MAX(Data[Date______________]),FILTER(Data,Data[Job______]=EARLIER(Data[Job______])&&Data[Period___]<>BLANK())),Data[Job______],Data[Job______])

Hope this helps.

[alexburn]

Thank you, this worked perfectly!

[Ashish_Mathur]

You are welcome.

[Portrek]

Hi Alexburn

You can create a custon column using the code below.

Max = CALCULATE(MAX(Table[Progress____]),ALLEXCEPT(Table,Table[Job______]))

Best regards.

[alexburn]

This does not work because the latest date is not always the maximum progress.

Also, it is not checking the [Period] column for blanks to exclude those from the calculation.

[Greg_Deckler]

@alexburn Seems like:

LatestProgress Column = 
  VAR __Job = [Job]
  VAR __Table = FILTER('Table',[Job] = __Job && NOT(ISBLANK([Period])))
  VAR __Max = MAXX(__Table,[Date])
RETURN
  MAXX(FILTER(__Table,[Date] = __Max), [Progress])

[alexburn]

I have tried this and the result returned without error, however, it is not the correct result.

See below example/result (filter to just one job):

Job_____	Period_____	Date_____	Progress_____	LatestProgres_____
11111	N3	01 July 2021	-100	0
11111	N2	01 June 2021	-100	0
11111	N1	01 May 2021	-100	0
11111	C11	01 March 2021	0	0
11111	C12	01 April 2021	0	0
11111		01 April 2022		0
11111		01 March 2022		0
11111	N4	01 August 2021	-100	0
11111	N5	01 September 2021	-100	0

I'm not sure if the (negative) -100 has anything to do with it?

I've tried MINX instead of MAXX to test this, but the result was the same.