Disaggregating cumulative data into hourly figure

[Sekelo77]

Hi Team,

Can you please help me here: with Power BI approach or an SQL query, any will be appreciated.

I have data that is cumulative, from morning which is the start of shift, untill the following day morning, which is the end of shift. What I need help or guide on is an approach on how I can get the values disagreggated from the raw data which is cumulative from the start of shift untill end of shift, I want it to an hourly value (more like the difference between the hours):

Here is the raw data from the table below:

Note: The green highlited is what I want as a result, which I can do easily on excel.
Thank you in advance.
Table1: 

CalendarDate	Sequence	TimeStamp	DateTimeStamp	Scale1_Cumulative	Scale1_Hourly
225/08/05	24	06:00:00	2025/08/05 05:59	18534	748
2025/08/05	23	05:00:00	2025/08/05 05:00	17786	854
2025/08/05	22	04:00:00	2025/08/05 04:00	16932	834
2025/08/05	21	03:00:00	2025/08/05 03:00	16098	968
2025/08/05	20	02:00:00	2025/08/05 02:00	15130	928
2025/08/05	19	01:00:00	2025/08/05 01:00	14202	-513
2025/08/04	18	00:00:00	2025/08/04 23:59	14715	826
2025/08/04	17	23:00:00	2025/08/04 23:00	13889	921
2025/08/04	16	22:00:00	2025/08/04 22:00	12968	727
2025/08/04	15	21:00:00	2025/08/04 21:00	12241	896
2025/08/04	14	20:00:00	2025/08/04 20:00	11345	931
2025/08/04	13	19:00:00	2025/08/04 19:00	10414	937
2025/08/04	12	18:00:00	2025/08/04 18:00	9477	927
2025/08/04	11	17:00:00	2025/08/04 17:00	8550	974
2025/08/04	10	16:00:00	2025/08/04 16:00	7576	938
2025/08/04	9	15:00:00	2025/08/04 15:00	6638	868
2025/08/04	8	14:00:00	2025/08/04 14:00	5770	844
2025/08/04	7	13:00:00	2025/08/04 13:00	4926	502
2025/08/04	6	12:00:00	2025/08/04 12:00	4424	800
2025/08/04	5	11:00:00	2025/08/04 11:00	3624	525
2025/08/04	4	10:00:00	2025/08/04 10:00	3099	714
2025/08/04	3	09:00:00	2025/08/04 09:00	2385	731
2025/08/04	2	08:00:00	2025/08/04 08:00	1654	860
2025/08/04	1	07:00:00	2025/08/04 07:00	794	794

[sanalytics · Accepted solution]

@Sekelo77 
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

[DataNinja777 · Accepted solution]

Hi @Sekelo77 ,

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,

[danextian · Accepted solution]

Hi @Sekelo77 

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

[DataNinja777 · Accepted solution]

Hi @Sekelo77 ,

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,

[Sekelo77]

Thank You very much 🙏

[sanalytics · Accepted solution]

@Sekelo77 
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

[Sekelo77]

Thank you, very much appreciated !

[MFelix]

Hi @Sekelo77 ,

Not sure if I understand what is your current data source but I assume it's a SQL query. You should push the calculations the further back has possible, in this case to SQL engine.

Check this video with an explanation on how to get difference from previous row.

https://www.youtube.com/watch?v=-kpPNWKv3Qw

[Sekelo77]

Thank, I think the video is explicit.