Cumulative count over time

[Ikeumlaut]

Hey,

I need to standardize my data. I get at irregular intervals a value for my devices. Message is sent when status changes. Here is an example data and what I mean by standardizing. In this example is to convert time to 10min sections.

10A	Time	10:11	10:14	10:18			10:48
	Value	1	0	1	0
20B	Time	10:18		10:34		10:51
	Value	0		1		0
Would look like this if "cumulative"  in 10min sections->
10A&20B	Time	10:10	10:20	10:30	10:40	10:50
	Cumulative value	1	1	2	1	0

Here is how the data looks like:

Time	Device	Value
28.3.2018 10:11	10A	1
28.3.2018 10:14	10A	0
28.3.2018 10:18	10A	1
28.3.2018 10:48	10A	0
28.3.2018 10:18	20B	0
28.3.2018 10:34	20B	1
28.3.2018 10:51	20B	0

How do I write a Measure to convert the data into a table visual like this?

Time	Cumulative value
28.3.18 10:10	1
28.3.18 10:20	1
28.3.18 10:30	2
28.3.18 10:40	1
28.3.18 10:50	0

Thanks!

[v-yulgu-msft]

Hi @Ikeumlaut,

One concern: 

How to group those time values based on 10 min interval? 

Why is 10:11 for 10A matched with 10:18 for 20B? Why should these two values belong to 10:10? While 10:14 for 10A and 10:18 for 10A belong to 10:20 and 10:30 section.

Best regards,

Yuliana Gu

[Ikeumlaut]

Hey @v-yulgu-msft

sorry, that is a mistake on the chart. I edited my post and it messed up it. So the grouping is:

• 10:00
  • 10:11, 10:14, 10:18

Best,

Ike

[v-yulgu-msft]

Hi @Ikeumlaut,

I am still confused about this calculation. That case, the Cumulative value for "28.3.18 10:10" in result table should be 2 rather than 1. Also, the Cumulative value for "28.3.18 10:20" in result table should be 0.

Regards,

Yuliana Gu

[Ikeumlaut]

Hey,

I do not want the sum of the values. Instead the cumulative distinct count of devices that have sent a value of 1. So I want to know how many devices have a value of 1 at each time interval. Sometimes the device sents the value at the interval, but sometimes it has sent the message right before the interval so I need to take that into account somehow.

Thank you for helping out!

Best,

Ike

[Ikeumlaut]

Hey @v-yulgu-msft,

here is a picture of what I'm looking for. Hope this explains it better.

Best,

Ike