Time series interpolation - comparing different granularities

[Anonymous]

Hi All,

I have a single fact table with multiple observed and modelled values from multiple assets, for comparison.

Some of the assets are measured in 2-minute time steps, others in 15-minutes (see image below).  

I need to compare these with a multi-line chart, interpolating the 15-minute data to join the dots with lines. In excel I would simply plot a scatter with lines. 

I've read this post from @Greg_Deckler tried various datetime formats, including a text timekey, explored custom visuals and am currently attempting to interpolate using a python script. 

This is the final sticking point at the end of a very long road. Any advice would be gratefully received!

[v-kkf-msft]

Hi @Anonymous ,

Have you ever tried to change the type of the X-axis to "Continuous"? Does this match your desired output?

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-kkf-ms,

thank you for responding.

Setting X to continuous was the first approach I made - the dropdown immediately switches back to categorical. I'm guessing my table structure is creating a conflict.

In your working example (thank you), there is a column for each asset ('tag'). In my fact table, there is a column "site" and another "tag" (concatenated as SourceTag in the image below) - values are returned in the column "value" (ignoring the fact these are zeros in the example below). 

One post suggested basing the X axis on a text field "DateTimeKey" would enable a continuous X axis, but this still reverts back to categorical. 

Apologies, I cannot post the model itself. 

Thank you again for your help 🙂   

[v-kkf-msft]

Hi @Anonymous ,

You can try to fill in null values.

1. First pivot column SourceTag.

2. Then fill up/down the null values. 

3. Select column DateTime and unpivot other columns.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-kkf-ms,

On step 1. pivot Source.Tag, I get the error below:

 

n.b. there is no row in the fact table for the blanks in my original post; there are 7 x two-minute timesteps for every 15-minute timestep. 

I'm trying using a measure to hold the previous value, then referencing a second measure = if ( [Value] = BLANK() , [PreviousValue] , [Value] ), but need to work in conditions for source.tag and datetime. 

I'll post a solution if I find one...

Thanks for your help. 

[v-kkf-msft]

Hi @Anonymous ,

According to your ideas, I found the following way to achieve.

First create a new calculation table "DateTime". And create a relationship with the Table. 

DateTime = DISTINCT('Table'[DateTime])

Then create a measure, using the DateTime column in the DateTime table as the X axis of the line chart.

Measure = 
var Pre_DT = 
    CALCULATE(
        MAX('Table'[DateTime]),
        FILTER(
            ALLEXCEPT('Table', 'Table'[SourceTag]),
            'Table'[DateTime] < MAX(DateTime[DateTime])
        )
    )
var Pre_Value = 
    CALCULATE(
        SUM('Table'[Value]),
        ALL(DateTime),
        'DateTime'[DateTime] = Pre_DT
    )
return 
    IF(
        SUM('Table'[Value]) <> BLANK(),
        SUM('Table'[Value]),
        Pre_Value
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.