Forum Discussion

JuliaYebra's avatar
JuliaYebra
Helper III
8 years ago
Solved

Last reading

Hello, I have this dataset with different temperature readings for same items. Is it any way to only keep the last reading for each item? This would be the yellow records. Thanks
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Go to Modeling and hit New Table.

     

    NewTable = SUMMARIZE(
	FILTER(
		ADDCOLUMNS(
			TableName,
			"Last Reading", VAR lastreadingdate = CALCULATE(
				MAX(TableName[Date]),
				ALLEXCEPT(TableName, TableName[Item])
			)
			RETURN IF(TableName[Date] = lastreadingdate, TRUE, FALSE) 
		),
		[Last Reading] = TRUE
	),
	TableName[Machine], TableName[Item], TableName[Date], TableName[Temperature]
)
fhill's avatar
fhill
Resident Rockstar
8 years ago

 

There are probably several ways to do this, but here's one option:  

 

DAX Column 1:  This calculates the MAX DateTime for each Item && Machine grouping.

LastReading_DateTime = CALCULATE(MAX(Table2[DateTime]), FILTER(ALL(Table2), Table2[Machine] = EARLIER(Table2[Machine]) && Table2[Item] = EARLIER(Table2[Item])))

 

DAX Column 2:  This only returns a TEMP if the LastReading_DateTime matches the original table's DateTime

LastReading_Temp = IF (Table2[DateTime] = Table2[LastReading_DateTime],Table2[Temp],BLANK())

 

(Sorry my DateTime is descending, but you get the idea.)