Sum last values by max date

[Anonymous]

Hello, guys!

Need some help to create a measure.

Have a table:

date	link_id	area	ppm	publication_date
17.08.2020	124662695	5	1000	07.08.2020
17.08.2020	127645504	50	600	11.08.2020
24.08.2020	148543021	37	500	15.08.2020
24.08.2020	127645504	50	640	20.08.2020
31.08.2020	148543021	37	600	15.08.2020
31.08.2020	127645504	50	800	20.08.2020

Need to receive the SUM of the latest values PPM for each LINK_ID betweendates (07.08.2020-30.08.2020) by Date.

In this case the right answer is 2 140

[Greg_Deckler · Accepted solution]

@Anonymous - This is sort of Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

In your case, maybe:

Measure = 
  VAR __Table = 
    ADDCOLUMNS(
      SUMMARIZE('Table',[link_id],"__Date",MAX([date])),
      "__PPM",MAXX(FILTER(ALL('Table'),[link_id]=EARLIER([link_id]) && [date]=[__Date]),[ppm)
    )
RETURN
  SUMX(__Table,[__PPM])

[Anonymous]

Thank you @Greg_Deckler  so much!

It works brilliant!

[Greg_Deckler]

Sweet!