Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Subtracting two rows

I am working on a data set, which includes information for about around 30 meters. These meters acted like counters (read data, then add the values on the previous ones meaning all the time increasin...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hello @ehsanbh ,

    Sorry to respond late.

    For question 1:

    If you have about 25-30 similar parameters in this dataset, you might consider creating a measure instead of a calculated column to do this because the computed column will occupy the actual memory. The measure will be as this:

    Measure =
VAR a =
    SUM ( 'Table'[Index] ) - 1
VAR previous =
    CALCULATE (
        MAX ( 'Table'[EL,AX12(MWh)] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = a )
    )
RETURN
    IF ( ISBLANK ( previous ), 0, SUM ( 'Table'[EL,AX12(MWh)] ) - previous )

    For question 2:

    Calculated column:

    Column =
VAR _lastrow =
    CALCULATE (
        SUM ( 'Table'[EL,AX12(MWh)] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
RETURN
    IF ( ISBLANK ( _lastrow ), 0, [EL,AX12(MWh)] - _lastrow )

    The measure is as above. Here is the result by using the measure:

    result.png

    Attached a sample file in the next one, hopes to help you.

    Best Looks,
    Yingjie Li

    If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

@amitchandak

Sorry to be confusing! Here is one example. I guess I have solved it half a way! I have EL, AX12 (MWh), which is electricity meter values (adding on previous value on hourly base) and I need a new column for absolute consumption for each specific hour. In this case I need to subtract the value of each row from the previous one.

I have used below DAX formula for a new calculated column and actually it works to some extent:

EL, AX12, abs

raw_measurement[EL, AX12 (MWh)]

- CALCULATE (

SUM ( raw_measurement[EL, AX12 (MWh)] ),

FILTER ( raw_measurement, raw_measurement[Index] = EARLIER ( raw_measurement[Index] ) - 1 )

)

02.JPG

Two questions:

1- I have around 25-30 similar parameters in this data set. Is this the way to go forward? I mean creating a calculated column for each parameter? Or is there a more efficient way to perform this?

2- As you see the first value has not been changed. How can I change the code to have zero for the first value?

v-yingjl's avatar
v-yingjl
Community Support
5 years ago

Hello @ehsanbh ,

Sorry to respond late.

For question 1:

If you have about 25-30 similar parameters in this dataset, you might consider creating a measure instead of a calculated column to do this because the computed column will occupy the actual memory. The measure will be as this:

Measure =
VAR a =
    SUM ( 'Table'[Index] ) - 1
VAR previous =
    CALCULATE (
        MAX ( 'Table'[EL,AX12(MWh)] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = a )
    )
RETURN
    IF ( ISBLANK ( previous ), 0, SUM ( 'Table'[EL,AX12(MWh)] ) - previous )

For question 2:

Calculated column:

Column =
VAR _lastrow =
    CALCULATE (
        SUM ( 'Table'[EL,AX12(MWh)] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
RETURN
    IF ( ISBLANK ( _lastrow ), 0, [EL,AX12(MWh)] - _lastrow )

The measure is as above. Here is the result by using the measure:

result.png

Attached a sample file in the next one, hopes to help you.

Best Looks,
Yingjie Li

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.