Subtract rows from a cumulative column value

[frandb]

Hi !

I'm having an issue calculating the difference between the value of two succesives rows as shown in the image. For example I want to substract the values of the column "Cons 1 kWh" for every row at every consecutive time.

I´ve tried several ways that I´ve found in the forum but none of them solved my problem successfuly. As an example I´ve tried to create an index column to use as a guide in de DAX code but the data base is always refreshing so afeter a few minutes the index column isn´t correct any more.

Could you help me please?

Thanks !

[Zubair_Muhammad]

@frandb

Try this column

Column =
VAR RowWithPreviousTime =
    TOPN (
        1,
        FILTER ( Table1, Table1[DATETIMEPLC] < EARLIER ( Table1[DATETIMEPLC] ) ),
        [DATETIMEPLC], DESC
    )
RETURN
    Table1[Cons 1 kWh] - MINX ( RowWithPreviousTime, [Cons 1 kWh] )

[Zubair_Muhammad]

@frandb

See attached file as well

[frandb]

Thanks !

But running the answer you told me this message apears: "There's not enough memory to complete this operation. Please try again later when there may be more memory available."

I think this problem is due to the excesive amount of data I have to process (1.700.000 rows).

Do you have an other idea?

[v-piga-msft]

Hi @frandb,

How do you connect your data source? Direct Query or Impot?

What 's size of your RAM space? From the message, it seems that you may add your RAM Space.

In addition, to get the better performance, you may need to optimize your data model.

Best Regards,

Cherry