Disaggregating cumulative data into hourly figure

Anonymous 
if you are looking for Power BI approach, i would rather suggest you to create this column in Power query. Below is the complete code that can help you.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZRbrsMgDES3UuW7lcAPbLqVqvvfRrFxqqubqRSFxPgQM57weh1NH80f1EiP+0Gybm08W1tXPH4nb02fOleou3Jkmfjxvv/nOaEffIa6mY81ugrgKSCBvBQ/JkeWM+J7QAx5Pvk2fY1zoPoTIshT8do5xkmA76FQ65Dvxct6XeNjrbMXkL8LRGlJ1wLfyRtxNUCsR6rTALxlF37wuwB2n7mBDviRXYD8KQCFdMsAZIDfXYD8KQCRRJt8ovoluwD5CvXOEqmTUf2cXUB8RmO2SX5lMqqfsgmQ9x2aYpbyIbxnDyBuO+SquYoJwLdBIT52yNRGFu9XPN2hkK6fbwyO1vkAtKf8kK5fT81yBwJKtxQf0uU7mRSV63L/hR4pPaTLdSJ5Nvl6udBbeUiX53gkrSv9QksKD+kKcZshrXWw7zzwoN9a+Y3YUyBk1zzuoNuan8eV5r4H2HcedtBsrcxmU+r+fn8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [alendarDate = _t, Sequence = _t, TimeStamp = _t, DateTimeStamp = _t, Scale1_Cumulative = _t, Scale1_Hourly = _t]),
TypeChanged = Table.TransformColumnTypes(Source,{{"alendarDate", type date}, {"Sequence", Int64.Type}, {"TimeStamp", type time}, {"DateTimeStamp", type datetime}, {"Scale1_Cumulative", Int64.Type}, {"Scale1_Hourly", Int64.Type}}),
Result =
let
varRowsSorted =Table.Sort(TypeChanged,{{"Sequence", Order.Ascending}}),
IndexAdded = Table.AddIndexColumn(varRowsSorted, "Index", 0, 1, Int64.Type),
Calculation = Table.AddColumn( IndexAdded,"Scale1_HourlyPQ", each try  _[Scale1_Cumulative] - IndexAdded[Scale1_Cumulative]{[Index]-1} otherwise IndexAdded[Scale1_Cumulative]{0}
),
ActualTable = Table.Sort(Calculation,{{"Sequence", Order.Descending}}),
TableColumn = 
Table.SelectColumns(ActualTable,
Table.ColumnNames(TypeChanged) & {"Scale1_HourlyPQ"})
in
TableColumn
in
Result

in SQL, it is very much easy.. Use LAG window function for creating this column.

Attaching the pbix file for your reference.

Hope this help you.

Regards,

sanalytics

Hi Anonymous ,

Disaggregating cumulative data into hourly figures is a common data transformation task. The fundamental logic involves calculating the difference between the cumulative value of the current time period and that of the immediately preceding time period. This can be accomplished effectively using either SQL if you are working directly with a database, or within Power BI's suite of tools.

If your data resides in a modern SQL database, the most efficient solution is to use a window function called LAG(). This function can access data from a previous row within the same result set, based on a specified order, which eliminates the need for complex joins. The query below demonstrates how to calculate the hourly figure by subtracting the previous hour's cumulative total from the current one.

SELECT
    CalendarDate,
    Sequence,
    TimeStamp,
    DateTimeStamp,
    Scale1_Cumulative,
    Scale1_Cumulative - LAG(Scale1_Cumulative, 1, 0) OVER (ORDER BY DateTimeStamp ASC) AS Scale1_Hourly
FROM
    Table1
ORDER BY
    DateTimeStamp DESC;

In this SQL statement, the LAG(Scale1_Cumulative, 1, 0) function retrieves the Scale1_Cumulative value from the row that is one position before the current row. The OVER (ORDER BY DateTimeStamp ASC) clause is critical; it instructs the database to order the rows by the timestamp chronologically before applying the LAG function, ensuring the "previous" row is correct. The third argument, 0, serves as a default value for the very first row in the set, for which no previous row exists, making the calculation correct from the start.

Alternatively, this transformation can be handled within Power BI. One approach is to use the Power Query Editor to reshape the data as it is being loaded. This method involves sorting the data chronologically, adding a temporary index column to identify row positions, and then adding a custom column to perform the calculation. The M code for the custom column would reference the previous row using its index.

if [Index] = 0 then [Scale1_Cumulative] else [Scale1_Cumulative] - #"Added Index"{[Index]-1}[Scale1_Cumulative]

This M language formula checks if the row is the first one (with an index of 0) and, if so, uses its own cumulative value. Otherwise, it subtracts the Scale1_Cumulative value from the previous row. The expression #"Added Index"{[Index]-1} is the syntax for looking up a value in the table from the previous step (#"Added Index") at the specified row ([Index]-1). After the calculation, the helper index column can be removed.

Another Power BI method is to create a DAX calculated column directly in your data model. This approach does not alter the source table but adds a new column with the computed hourly values. The DAX formula must manually identify the previous timestamp and then look up its corresponding cumulative value to perform the subtraction.

Scale1_Hourly =
VAR vCurrentTimestamp = Table1[DateTimeStamp]
VAR vPreviousValue =
    CALCULATE (
        MAX ( Table1[Scale1_Cumulative] ),
        FILTER (
            ALL ( Table1 ),
            Table1[DateTimeStamp]
                = MAXX (
                    FILTER ( ALL ( Table1 ), Table1[DateTimeStamp] < vCurrentTimestamp ),
                    Table1[DateTimeStamp]
                )
        )
    )
RETURN
    Table1[Scale1_Cumulative] - IF ( ISBLANK ( vPreviousValue ), 0, vPreviousValue )

This DAX code uses variables for clarity and efficiency. It first finds the latest timestamp that is earlier than the current row's timestamp. It then uses CALCULATE to find the cumulative value associated with that previous timestamp. Finally, it subtracts this previous value from the current row's cumulative value, using an IF(ISBLANK(...)) check to handle the first row of the dataset gracefully by subtracting zero. For data preparation, the SQL or Power Query methods are often preferred as they pre-calculate the values, potentially leading to better report performance.

Best regards,

Hi Anonymous 

Try the following

Hourly Value Calc Column  = 
VAR _prevSequence = CumulativeData[Sequence] - 1
VAR _prevSequenceValue =
    MINX (
        FILTER ( CumulativeData, CumulativeData[Sequence] = _prevSequence ),
        [Scale1_Cumulative]
    )
RETURN
    CumulativeData[Scale1_Cumulative] - _prevSequenceValue

Hourly Value Measure = 
VAR _prevSequence =
    SELECTEDVALUE ( CumulativeData[Sequence] ) - 1
VAR _prevSequenceValue =
    MINX (
        FILTER ( ALL ( CumulativeData ), CumulativeData[Sequence] = _prevSequence ),
        [Scale1_Cumulative]
    )
RETURN
    SELECTEDVALUE ( CumulativeData[Scale1_Cumulative] ) - _prevSequenceValue