Forum Discussion

lbar's avatar
lbar
Frequent Visitor
8 years ago

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

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

Data Modeling

7 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      lbar
      Frequent Visitor
      8 years ago

      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's avatar
        sirros_iot
        Helper III
        8 years ago

        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...

         

         

    • lbar's avatar
      lbar
      Frequent Visitor
      8 years ago

      Hello Dale,

       

      Have you been able to puzzle out a solution?

       

      Best Regards,

      lbar

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        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] ) )
    )
)

        How_to_calculate_difference_between_consecutive_rows_and_cumulative_sum_without_R_script

         

        Best Regards,

        Dale

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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