How to create four new columns that have most recent data point for each

[TheKiltedGolfer]

In one data set/query, I have two DC names and from each I need to pull the most recent CPU % and Memory % from those (See attached).   I think it would be best to create 4 new columns (Latest %CPU RDC/GDC, Latest %Mem RDC,/GDC Latest %CPU West Campus, and Latest %Mem West Campus).  I have no idea if there is a DAX formula that will work or some other way that I can do this.  As you can see, each day has two rows of data for four data points that I need to extract but only from the most recent date.  I am sure this is something that is commonly done (pull specfic data from the most recent date) but I am not sure where to start.  If there is a better way to accomplish this then please let me know.  I will be trending the four data points over time in a line graph and have a dial guage that will display the most recent values.

Thank You

[dramus]

Create a new Column for each.

Latest CPU %=

var LatestEntry=CALCULATE(LASTDATE('Table1'[date_time]),filter('Table1','Table1'[DC Name]=EARLIER([DC Name])))

return maxx(filter(Table1,[date_time]=LatestEntry &&'Table1'[DC Name]=EARLIER([DC Name])),[CPU %])

[TheKiltedGolfer]

Thank you.  I was trying to us 'Lookupvalue' with not as much success as I was hoping.  However, when I am trying to use your solution I am getting a "Token Eof expected" error and when I select 'Show error' then 'LatestEntry' is highlighted.  Any suggestions?

[dramus]

I just copy and pasted the column definition back into PBI Desktop without an issue. 

[TheKiltedGolfer]

Yeah... I am doing something stupid because it is not working.

[Greg_Deckler · Accepted solution]

So, here is an example that you can extrapolate upon to get the rest.

Most Recent # CPUs = CALCULATE(MAX(Table[# CPUs]),FILTER(Table,[date_time]=MAX(Table[date_time])))

[TheKiltedGolfer]

I think I discovered why it was not working for me.  I needed to enter your solution via New Column under Modeling and not DAX

[Greg_Deckler]

Are you trying to enter those formulas in Query Editor? That will not work as that uses M code instead of DAX. You need to go to the Modeling tab and choose New Column and then paste in the code provided.

[TheKiltedGolfer]

I am trying to do something simple just to see if I can get anything to work.. I tried the following:

CALCULATE(MAX(RDC/WC_MEM_and_CPU[date_time]))

I do not get any syntax errors yet I to get Expression Error: The name 'CALCULATE' wasn't recognized.  Is there some sort of setting I need to correct?

[TheKiltedGolfer]

I renamed my column from # CPUs to #CPUs but still got an error of invalid identifier with #CPUs highlited.  What I entered:

Custom = CALCULATE(MAX(RDC/WC_MEM_and_CPU[#CPUs]),FILTER(RDC/WC_MEM_and_CPU[date_time]=MAX(RDC/WC_MEM_and_CPU[date_time])))

I also tried this with the comma after Filer Table per your note:

CALCULATE(MAX(RDC/WC_MEM_and_CPU[#CPUs]),FILTER(RDC/WC_MEM_and_CPU,[date_time]=MAX(RDC/WC_MEM_and_CPU[date_time])))