Mixing two time series - use forecast if measured value missing

[BI2018No]

Hello!

Is it possible to mix two time series from a table? One default serie is returned, but if value here is missing a value from another series is returned. Primary serie contains measured values, but sometimes one og more hourly values are missing, and we would like to use a forecasted value from another serie.

The new measure/column should return a value for each date. Primary value is ID1 but for the missing date value from ID2 is used.

ID	Date	Value
1	01.01.2018	1
1	02.01.2018	2
1	03.01.2018	Missing!
1	04.01.2018	4
1	05.01.2018	5
2	01.01.2018	6
2	02.01.2018	7
2	03.01.2018	8
2	04.01.2018	9
2	05.01.2018	10
New ID	01.01.2018	1
New ID	02.01.2018	2
New ID	03.01.2018	8
New ID	04.01.2018	4
New ID	05.01.2018	5

[v-yulgu-msft · Accepted solution]

Hi @BI2018No,

Suppose the ID values are continual numerics in your fact dataset. 

new column =
IF (
    TableA[Value] = BLANK (),
    CALCULATE (
        FIRSTNONBLANK ( TableA[Value], 1 ),
        FILTER (
            TableA,
            TableA[ID]
                = EARLIER ( TableA[ID] ) + 1
                && TableA[Date] = EARLIER ( TableA[Date] )
        )
    ),
    TableA[Value]
)

However, if not, please add an index column in Query Editor. Then, refer to this index column rather than ID column in above formula.

Regards,

Yuliana Gu