How to calculate difference between consecutive rows and cumulative sum without R script

[lbar]

I search for a possibility in Power Query or DAX, to calculate the difference between consequtive row values and store the result in a new column. The table is sorted e.g. along a timestamp and e.g. the time differences should be calculated each row to the previous.

Or if we have a measured signal and would like to calc the first difference of this signal.

Does Power Query M-Language (or DAX) provide some built-in function for this task? Or is there any possibility to achieve this WITHOUT R script.

I'm looking also for a possibility to calc the cumulative sum according to the same scheme

[Zubair_Muhammad]

Hi @lbar

The Query Editor/ Power Query allows you to add an Index Column in your Table

Using this Index Column you can do the Difference and Cumulative Calculations

[v-jiascu-msft]

Hi @lbar,

It seems possible. Please provide a dummy sample and result you want. The pbix file would be great. Please mask your sensitive data first.

Best Regards,

Dale

[lbar]

Hello Dale,

Have you been able to puzzle out a solution?

Best Regards,

lbar

[v-jiascu-msft]

Hi @lbar,

Power Query is a little difficult to me. How about Dax? There are two approaches. You can try it out in this file.

Adding an index in the Query Editor is necessary for both methods.

1. Small steps.

diff =
VAR previousPowerOn =
    CALCULATE (
        SUM ( Table1[PowerOn] ),
        FILTER ( 'Table1', Table1[Index] = EARLIER ( Table1[Index] ) - 1 )
    )
VAR diff =
    IF ( ISBLANK ( previousPowerOn ), 0, [PowerOn] - previousPowerOn )
RETURN
    IF ( diff < 0, 0, diff )

SmallSteps =
IF (
    [PowerOn] = 0,
    0,
    CALCULATE (
        SUM ( Table1[diff] ),
        FILTER ( 'Table1', 'Table1'[Index] <= EARLIER ( Table1[Index] ) )
    )
)

2. One step.

OneStep =
IF (
    [PowerOn] = 0,
    0,
    CALCULATE (
        SUMX (
            ADDCOLUMNS (
                SUMMARIZE ( 'Table1', Table1[Index], Table1[PowerOn] ),
                "diff2", IF (
                    ISBLANK (
                        CALCULATE (
                            SUM ( Table1[PowerOn] ),
                            FILTER ( 'Table1', Table1[Index] = EARLIER ( 'Table1'[Index] ) - 1 )
                        )
                    ),
                    0,
                    [PowerOn]
                        - CALCULATE (
                            SUM ( Table1[PowerOn] ),
                            FILTER ( 'Table1', Table1[Index] = EARLIER ( 'Table1'[Index] ) - 1 )
                        )
                )
            ),
            IF ( [diff2] < 0, 0, [diff2] )
        ),
        FILTER ( 'Table1', 'Table1'[Index] <= EARLIER ( Table1[Index] ) )
    )
)

Best Regards,

Dale

[lbar]

Hello Dale,

Thank you very much for your contribution, and sorry for my very late response...

I have integrated it and it's working correctly.

Unfortunately, it has computationnaly exponential complexity because for every new value it's summing up all previous values! This is why it works only for a few thousand rows.

But I have some tens of thousand up to more than half a million rows.

In Power Query I have also implemented it, but it seems that there is also no other possibility.

Does anyone know any other (computationally cheaper) way (except R)?

Regards,

lbar

[lbar]

Hello v-jiascu-msft,

Below a screenshot of the data.

time (ordered ascending) and PowerAplitude are imported, PowerOn is a conditional column and ContourNumber is calculated with the below R scprit.

Problem: How can I do the operations of the R script (diff, cumsum) directly with M-functions? One limitation is, that the operations must not involve searching, because of performance. In the real task we have up to some 10^5 datarows.

Background: R is not suitable in our use case, because it doesn't allow to refresh directly on cloud data. It only works on-premise.

(unfortunately I can't upload the sample pbix, or do you know how ?!)

[sirros_iot]

Hello Ibar

I have a problem similar to this one and I didn't find out how to solve it yet. I'm getting the data from a device( arduino), so, I can't create work with power query, no calculated columns. Any idea of solution, I would be very happy, work thing kkk.

In addition on the post, I rank my data with timestamp but I don't know how to calculate de difference between times.

Good luck...