Forum Discussion

alexburn's avatar
alexburn
New Member
5 years ago
Solved

Latest value after filters

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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    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.

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
alexburn
New Member
5 years ago

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_____
11111N301 July 2021-1000
11111N201 June 2021-1000
11111N101 May 2021-1000
11111C1101 March 202100
11111C1201 April 202100
11111 01 April 2022 0
11111 01 March 2022 0
11111N401 August 2021-1000
11111N501 September 2021-1000

 

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.