Fill blank values of timeseries data in datetime resolution, based on most recent nonblank value.

[ottor]

Hi everyone,

I have a table containing on/off (0,1) signals from two devices, which don't have continuous time axes, i.e. only the changes are recorded and they can occur at any time. Now I need to visualize these signals in a continuous way and fill the blank values, for which I don't have any signal info, with the latest non-blank value of the corresponding device.

There are two tables, one for the signal data and one for the continuous DateTime values in hourly resolution.

SignalTableDateTimeTable

I found a couple of working solutions for cases, which have only dates as time axes, but I am struggling to make this work with the datetime format of timestamps.

I started with the solution from this thread, which solves the problem for a "date" axis using a measure:

SIGNAL_GAPS_DATE = 
VAR timeFrame =
    DATESBETWEEN( 'DateTable'[Date], BLANK(), LASTDATE('DateTable'[Date] ) )
VAR lastNonBlankStatusTS =
    LASTNONBLANK ( timeFrame, CALCULATE ( COUNT ( Signal[Status] ) ) )   
RETURN
	CALCULATE( SUM( Signal[Status]) , lastNonBlankStatusTS )

I then tried to adapt this for timestamps, but I can't find a working way to replace the DATESBETWEEN function with something similar. For example I tried to use the VALUES statement in the measure like:

SIGNAL_GAPS_DATETIME = 
VAR timeFrame =
    VALUES(DateTimeTable[DateTime])
VAR lastNonBlankStatusTS =
    LASTNONBLANK ( timeFrame, CALCULATE ( COUNT ( Signal[Status] ) ) )
RETURN
    CALCULATE( SUM(Signal[Status]), lastNonBlankStatusTS)

Also the approach of this solution didn't help me with the issue.

I have uploaded a demo dashboard here. It has the working solution for dates on the left and the non-working solution for datetime on the right. The two plots / tables at the top are showing the raw signals and the bottom left one shows the desired result:

Demo Report

Is there a way to make this work with a measure? Thank you all in advance.

Best regards

ottor

[LivioLanzo]

Hi @ottor

I am taking a look at your tables. Why do you maintain a DateTime table at Hourly granularity if your Signal table is at the Day level (time is always 0 ) ?

[ottor]

Hi LivioLanzo,

Its an artifical table just for this example, the real data is then at least in minute resolution. I kept it at 00:00 to be able to

experiment with date and datetime in parallel.

Thanks for looking at this, its much appreciated.