Calculated measure in custom time series

[Ikeumlaut]

I have data from thousands of devices in a "raw" format where I have time of message sent from individual devices: message value (0 or 1), and device id. I want to create a 10min interval bar chart time series graph of this data, see example picture. I want to sum the values of all devices by their max value sent during the 10min interval, meaning if for example at 10:31 the devices value is 0 and at 10:34 values is 1 the value used to sum would be 1.

My thinking goes like this

1. Create a new table with all time stamps for the time series. I guess doing a summarized table of all dates and crossjoining it with a table with the 10min values would be the best way to go?
2. Create a releation ship between this new table and the raw data table
3. Create a Measure that does the calculation. Need help on this, how to do it
4. Create a bar graph where x-axis is the 10min time series and y-axis is the calculated measure

Am I on the right track and what would be the best way to solve this problem?

Here is the raw data format:

Time	DeviceID	Value
28.3.2018 14:14	10A	1
28.3.2018 13:58	10A	1
28.3.2018 13:38	10A	0
28.3.2018 13:06	10A	1
28.3.2018 12:49	10A	1
28.3.2018 12:33	10A	1
28.3.2018 12:17	10A	0
28.3.2018 11:49	10A	1
28.3.2018 11:32	10A	1
28.3.2018 10:48	10A	1
28.3.2018 10:16	10A	0
28.3.2018 14:13	20B	1
28.3.2018 13:57	20B	1
28.3.2018 13:41	20B	1
28.3.2018 13:25	20B	1
28.3.2018 13:21	20B	0
28.3.2018 12:49	20B	1
28.3.2018 12:33	20B	1
28.3.2018 12:17	20B	1
28.3.2018 12:01	20B	1
28.3.2018 10:18	20B	0

Here is how the data should look like to create the bar graph:

Time	Total Value
28.3.2018 14:10	2
28.3.2018 14:00	2
28.3.2018 13:50	2
28.3.2018 13:40	1
28.3.2018 13:30	1
28.3.2018 13:20	2
28.3.2018 13:10	2
28.3.2018 13:00	2
28.3.2018 12:50	2
28.3.2018 12:40	2
28.3.2018 12:30	2
28.3.2018 12:20	1
28.3.2018 12:10	1
28.3.2018 12:00	2
28.3.2018 11:50	1
28.3.2018 11:40	1
28.3.2018 11:30	1
28.3.2018 11:20	1
28.3.2018 11:10	1
28.3.2018 11:00	1
28.3.2018 10:50	1
28.3.2018 10:40	1
28.3.2018 10:30	0
28.3.2018 10:20	0
28.3.2018 10:10	0

and here the graph I'm aiming for:

Thanks!

[v-shex-msft]

HI @Ikeumlaut,

You can refer to below steps to achieve your requirement.

Steps:

1. Add calculated column category to original table.

Category =
LEFT ( [Time], LEN ( Table1[Time] ) - 1 )
    & "0"

2. Create new table to generate expand date range.

Table = 
VAR _date =
    DISTINCT (
        SELECTCOLUMNS (
            Table1,
            "Date", DATEVALUE ( SUBSTITUTE ( LEFT ( [Time], 10 ), ".", "/" ) )
        )
    )
RETURN
    SELECTCOLUMNS (
        CROSSJOIN (
            _date,
            SELECTCOLUMNS (
                CROSSJOIN (
                    GENERATESERIES ( 0, 23 ),
                    SELECTCOLUMNS ( GENERATESERIES ( 0, 5 ), "Minute", [Value] * 10 )
                ),
                "Time", TIME ( [Value], [Minute], 0 )
            )
        ),
        "DateTime", DATEVALUE ( [Date] ) + TIMEVALUE ( [Time] )
    )

3. Add calculated column to format datetime to specific category, setting sort by column 'datetime'.

Category = FORMAT([DateTime],"dd.m.yyyy hh:mm" )

4. Add relationship between two category columns.(setting 'both' for 'cross filter direction' option)

5. Add a measure to original table to calculate total value of same category.

Amount = SUM(Table1[Value])+0

6. Create line chart.

I also attached sample file.

Regards,

XXiaoxinShneg

[Ikeumlaut]

Hey @v-shex-msft,

thanks it seems to bee working! However, I realized my problem is a bit more complex 😄

I need to calculate a "cumulative sum" as each device does not sent every 10min interval a message. So if a device has not sent a message during the 10min interval I need to look what was the previous message on use that value, so "print" last value in time forward until a new values has been received from deivce. I found this topic/solution, but it is on a daily level.

Here is the matrix I have now:

Device	3.4.2018 12:10	3.4.2018 12:20	3.4.2018 12:30	3.4.2018 12:40	3.4.2018 12:50	3.4.2018 13:00	3.4.2018 13:10	3.4.2018 13:20	3.4.2018 13:30	3.4.2018 13:40
A	1		1	1		1		1	1
B	1				0	1	1		1
C	1		1			0
SUM Gives:	3	0	2	1	0	2	1	1	2	0

Here is how the data should look like:

Device	3.4.2018 12:10	3.4.2018 12:20	3.4.2018 12:30	3.4.2018 12:40	3.4.2018 12:50	3.4.2018 13:00	3.4.2018 13:10	3.4.2018 13:20	3.4.2018 13:30	3.4.2018 13:40
A	1	1	1	1	1	1	1	1	1	1
B	1	1	1	1	0	1	1	1	1	1
C	1	1	1	1	1	0	0	0	0	0
Should give :	3	3	3	3	2	2	2	2	2	2

Thanks!

[v-shex-msft]

Hi @Ikeumlaut,

Actually, I also onside how to display nonexistent records. For original table, some of records not real stored in it.

On my opinion, I'd like to suggest you use original table and calendar table to create new expand table with all detail records.(setting value of new records to zero)

After above steps, you can try to write a measure to replace displayed values.

Regards,

Xiaoxin Sheng