Latest reading value for each ID by day

[Anonymous]

Hi, 



I have different ID, and they record value which 0 or 1 through a day. 

I am trying to to make a latest reading calculated column which gives me the last recorded value for each ID either true or false. I am trying to use this formula but not getting my desire result. Pleasey modify. 

Latest reading tempratur = VAR mDate = CALCULATE( MAX(Test[Timestamp]), ALLEXCEPT('Test', 'Test'[ID]) ) RETURN 'Test'[Timestamp] = mDate && 'Test'[Value]

And the end result is this, which is wrong.

So i want this result example. 

ID                Valye             timpestamp               Latest reading - tempratur

13                  0                    13 juni                     
13                  1                     1 pm
13                  0                      2 pm
13                  0                      3 pm
13                  0                      3 pm
13                  1                       4 pm                            1
8                    0                       1 pm 
8                    1                        2 pm
8                    1                        3 pm
8                    1                        4 pm                            1
3                    0                        6 pm
3                    0                       7 pm                             0


Thanx in advance

[Anonymous]

Anyone??????

[HotChilli]

Column = VAR _id = Test[ID]
RETURN
IF (Test[Timestamp] = CALCULATE(MAX(Test[Timestamp]), ALLEXCEPT(Test, Test[ID])) && Test[ID] = _id, Test[Value], BLANK())

I think that works with your test data

[Anonymous]

@HotChilli 

Hi, It dosent give me the out I want. I just want the latest recorded of each day. If I click on true here this is another example, I get the last reading for every ID. When I click on false, I get every reading. This is what I want. Then I used the formula I have written here earlier, but his is not working here. 


Like under here, I click on true I get the latest recorded value for each ID. False gives me everything. I want the same for 1 and 0. I click on true. I get all the latest readings for my ID, if its 1 or its 0. Ur formula didnt work. 

[Anonymous]

anyone????????

[richbenmintz · Accepted solution]

Hi @Anonymous ,

If I understand you are trying to identify the max value per group, you can achieve you can achieve this as follows

-> Adding a column that stores the max by reading date time by group

Max Date Time By Group = CALCULATE(max(Table[timestamp]), filter('Table', Table[ID] = EARLIER([id])))

-> Adding a conditional column to either store the max value or a bool, your choice

Max Value = if([timestamp] = [Max Date Time By Group], [Reading], BLANK())
--or
Max Value Bool= if([timestamp] = [Max Date Time By Group], true(), false())

You could also combine the two columns into a single column by using a variable to hold results of the first column

Max Value with Variable = 
var Max_Date_Time_By_Group = CALCULATE(max(Table[timestamp]), filter('Table1', Table1[ID] = EARLIER([id])))
return 
if([timestamp] = Max_Date_Time_By_Group, [Reading], BLANK())

Hope this helps,