Forum Discussion
Last reading
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] )
Indeed there is. Go to Power Query to edit your data import. Select both your Machine and your Item column. Click Transform - Group By. In the dialog, change New Column Date to Date, Operation to Max and Column to Date. That should give you this:
But, we lost the temperature. We need to bring it back in. Load your data again using Recent Sources. I called your table Reading, so this gives me a Reading (2) query in the Query pane. Select your original query (Reading in my example) in the query pane and click Merge Queries. In the top portion, select the Machine, Item and Date columns (ctrl-click on each column). Then select your Reading 2 query in the drop down further down and select all three same columns. Press OK. You should now have this:
Click the small icon in the upper right corner of the Reading (2) column header to expand the tables. Select only the Temp item in the resulting pop-up. Rename the resulting Column to Temp.
The only remaining thing now is to right click the Reading (2) query and unselect the Enable Load menu item. This prevents this temporary query to from being loaded to your data model. Click close and apply and your should have the result you want.
here simple formula to add new column to identify last reading (most recent)
Last Reading = var r =
RANKX(
FILTER(Table1, Table1[Machine] = EARLIER(Table1[Machine]) &&
Table1[Item] = EARLIER(Table1[Item])), Table1[Date], , DESC, DENSE
) return if(r = 1, "Yes", "")