Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |