Subtracting values from the same column but different rows

[Anonymous]

Hi! 

I have an electricity meter that is measuring cumulatively but I need to calculate the difference, therefore, the electricity use each hour. All the electricity data is in one column and I need to, for example, calculate the difference between Row 2-Row 1 for all of the rows. Is there a way to do this in Power BI?

Many thanks for any help you can give me!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Take below simple data as an example.

To get the difference between rows via calculated column.

diff =
Table3[Value]
    - CALCULATE (
        SUM ( Table3[Value] ),
        FILTER ( Table3, Table3[Index] = EARLIER ( Table3[Index] ) - 1 )
    )

To get difference between rows via measure.

diff measure = 
SELECTEDVALUE ( Table3[Value] )
    - CALCULATE (
        SUM ( Table3[Value] ),
        FILTER (
            ALLSELECTED ( Table3 ),
            Table3[Index]
                = SELECTEDVALUE ( Table3[Index] ) - 1
        )
    )

For more advice, please provide your sample data and desired output. How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[tarunsachdeva]

Hi, I tried this and getting error message...

MdxScript(Model) (12,162) Calculation error in measure

[awoo_123]

Hi Yuliana, 

If i want to make Row 1 - Row 2 to get the difference, how should the measure look like?

Thank you in advance

[Greg_Deckler]

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...