Previous Value by Device & Time

Question

Hello Everybody,i've spent the whole afternoon with this dataset:&nbsp;mtsDeviceIDcOut19/10/2020 00:00A16.419/10/2020 00:05A16.419/10/2020 00:10A16.419/10/2020 00:15A16.419/10/2020 00:20A16.319/10/2020 00:25A16.219/10/2020 00:30A16.219/10/2020 00:35A16.219/10/2020 00:40A16.219/10/2020 00:45A16.219/10/2020 00:00B16.219/10/2020 00:05B16.119/10/2020 00:10B16.119/10/2020 00:15B16.119/10/2020 00:20B16.019/10/2020 00:25B16.019/10/2020 00:30B15.919/10/2020 00:35B15.919/10/2020 00:40B15.919/10/2020 00:45B15.9&nbsp;That I want to transform in this viewmtsHourDevice&nbsp;Average of cOut&nbsp;&nbsp;Difference&nbsp;19/10/2020 00:00A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,72&nbsp;19/10/2020 01:00A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,70-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0,0219/10/2020 02:00A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0,0019/10/2020 03:00A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,62-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0,0819/10/2020 04:00A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,53-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0,1019/10/2020 05:00A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,58&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0,0519/10/2020 00:00B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,57&nbsp;19/10/2020 01:00B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,54-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0,0319/10/2020 02:00B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,35-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0,1919/10/2020 03:00B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,08-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0,2719/10/2020 04:00B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,57&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0,4919/10/2020 05:00B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,66&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0,09&nbsp;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.&nbsp;Thank you for your help! Will be very appreciated!Max

amitchandak · Accepted Answer

Anonymous , not sure how got this avg of 20,
&nbsp;
Try a new column
calculate(lastnonblankvalue([mtsHour],max(Average of cOut)),filter(table, [Device] = earlier([Device]) &amp;&amp; [mtsHour] &lt;earlier([mtsHour])))
&nbsp;
or new measure
calculate(lastnonblankvalue([mtsHour],max(Average of cOut)),filter(allselected(table), [Device] = max([Device]) &amp;&amp; [mtsHour] &lt;max([mtsHour])))

Forum Discussion

Previous Value by Device & Time

2 Replies

Related content

Getting value for previous year based on value name and year slicer

Previous Row Value Dax

Previous Day Value

Previous Day Value

Previous date values

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

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