Handle non-periodic/breakpoint Timeseries

[JSiebrecht]

I have a PowerBI challenge with what some call aperiodic or non-periodic timeseries, some call them breakpoint.

Essentially it is a table with DateTime and Value pairs. And a value is always valid until the next DateTime/Value pair.

Challange:

Now I need to calculate the total volume (sum of value) for all hours of a day - with the value implicitly valid at these hours.

Example:

As you can see from 00:00 of May 23 the Value is 120. And this 120 is valid until the new Value of 145 at 6:00. This stays in place until comes the 110 at 20:00 etc.

The implication is illustrated in column G.

The total volume for that day would be 3300 as seen in call I29.

Problem is, my Timeseries table contains around 10mio such records. Where I to expand it to explicit records for all cases and all hours, I would end up with about 150mio records.

Anyone got an idea how to handle this with measures?

I DO have a calendar table with hourly granularity if this helps.

Many thanks!

Jan

[AlexisOlson]

FWIW, I'd hypothesize that the run-length encoding compression that Power BI uses would make expanding the records less of an issue than you might imagine. The cardinality (distinct count) of a column is more important than the actual number of rows.

However, that doesn't necessarily mean it's the best approach. You can indeed write a measure that iterates over each DateTime row in your calendar table (although you probably want to consider having separate Date and Time dimension tables). Something like this:

SUMX (
    'Date'[DateTime],
    VAR _CurrDateTime = 'Date'[DateTime]
    VAR _LastDateTime =
        CALCULATE ( MAX ( TimeSeries[DateTime] ), 'Date'[DateTime] <= _CurrDateTime )
    RETURN
        CALCULATE ( SUM ( TimeSeries[Value] ), 'Date'[DateTime] = _LastDateTime )
)