Calculate difference and conditional column between two timeseries tables

[simber]

Hi,

I have two tables where I want to calculate the difference and a conditional column between these tables, which should be filtererable for each category. Which means I want to be able to filter for each key with a slicer.

The difference should be between Value 1 and Value 2 for each timestamp.

The conditional calculation compares Value 1 and Value 2 in this way: if Value 1 > Value 2 then 1 else if Value 1<Value2 then -1 else 0

The following data is example data, real data consists of more keys and 1-2year of hourly data.

Table 1:

Date		key	Value 1
2022-01-01 00:00:00		500	5
2022-01-01 00:00:00		600	10
2022-01-01 01:00:00		500	2
2022-01-01 01:00:00		600	6

Table 2:

Date		key	Value 2
2022-01-01 00:00:00		700	9
2022-01-01 00:00:00		800	15
2022-01-01 01:00:00		700	11
2022-01-01 01:00:00		800	14

Table 3:

Keys	Tags
500	Price 1
600	Price 2
700	Price 3
800	Price 4

[v-luwang-msft]

Hi @simber ，

I see that you have two tables with two values at one point in time, are the two values comparing the sum or?

And what the table3 and keys in two tables work for ?

Best Regards

Lucien

[simber]

Hi Lucien,

For the conditional we compare for each point in time.

So if we filter for key 500 and key 700, we should get:

2022-01-01 00:00:00	-1
2022-01-01 01:00:00	-1

The third table is a dimensional table which can be used to connect table 1 and table 2.