Previous Value by Device & Time

[Anonymous]

Hello Everybody,

i've spent the whole afternoon with this dataset:

mts	DeviceID	cOut
19/10/2020 00:00	A	16.4
19/10/2020 00:05	A	16.4
19/10/2020 00:10	A	16.4
19/10/2020 00:15	A	16.4
19/10/2020 00:20	A	16.3
19/10/2020 00:25	A	16.2
19/10/2020 00:30	A	16.2
19/10/2020 00:35	A	16.2
19/10/2020 00:40	A	16.2
19/10/2020 00:45	A	16.2
19/10/2020 00:00	B	16.2
19/10/2020 00:05	B	16.1
19/10/2020 00:10	B	16.1
19/10/2020 00:15	B	16.1
19/10/2020 00:20	B	16.0
19/10/2020 00:25	B	16.0
19/10/2020 00:30	B	15.9
19/10/2020 00:35	B	15.9
19/10/2020 00:40	B	15.9
19/10/2020 00:45	B	15.9

That I want to transform in this view

mtsHour	Device	Average of cOut	Difference
19/10/2020 00:00	A	20,72
19/10/2020 01:00	A	20,70	-        0,02
19/10/2020 02:00	A	20,70	0,00
19/10/2020 03:00	A	20,62	-        0,08
19/10/2020 04:00	A	20,53	-        0,10
19/10/2020 05:00	A	20,58	0,05
19/10/2020 00:00	B	20,57
19/10/2020 01:00	B	20,54	-        0,03
19/10/2020 02:00	B	20,35	-        0,19
19/10/2020 03:00	B	20,08	-        0,27
19/10/2020 04:00	B	20,57	0,49
19/10/2020 05:00	B	20,66	0,09

What I need, in few words, is to have a new column that calculates the Difference between the AVERAGE of cOut (Temperature) between an Hour and another.

If too complicated I can also work on the non-grouped by mtsHour dataset and get a value for a single row so I can aggregate it later.

I've tried a ton of samples but no-one really works.

Thank you for your help! Will be very appreciated!

Max

[v-easonf-msft]

Hi, @Anonymous 
Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.
It will help other community members easily find the solution when they get the similar issue.
For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[amitchandak · Accepted solution]

@Anonymous , not sure how got this avg of 20,

Try a new column

calculate(lastnonblankvalue([mtsHour],max(Average of cOut)),filter(table, [Device] = earlier([Device]) && [mtsHour] <earlier([mtsHour])))

or new measure

calculate(lastnonblankvalue([mtsHour],max(Average of cOut)),filter(allselected(table), [Device] = max([Device]) && [mtsHour] <max([mtsHour])))