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____
11111N101 May 202150
11111N201 June 202160
11111 01 July 20210
22222N101 May 202180
22222N201 June 202190
22222N301 July 202190
22222 01 August 20210
33333 01 June 20210
33333N201 July 202120
33333 01 August 20210

 

What I need to end up with is this:

 

Job______Period___Date______________Progress____LatestProgress__
11111N101 May 20215060
11111N201 June 20216060
11111 01 July 2021060
22222N101 May 20218090
22222N201 June 20219090
22222N301 July 20219090
22222 01 August 2021090
33333 01 June 2021020
33333N201 July 20212020
33333 01 August 2021020

 

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.

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.

     

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    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.

  • Portrek's avatar
    Portrek
    Icon for Resolver III rankResolver III
    5 years ago


    Hi Alexburn

    You can create a custon column using the code below.

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

     

    Best regards.

    • alexburn's avatar
      alexburn
      New Member
      5 years ago

      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.