cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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 increasing - please look at the graphs). I get this information every hour. Then I need to calculate the absolute value for that hour. To reach this goal I need to subtract each row from the previous row. Can I create a general measure and use it for each of 30 columns? Please can you share any example formula in DAX that I can use? What is the best and most efficient way to deal with this? 

01.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Subtract two rows

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.

View solution in original post

3 REPLIES 3
Highlighted
Super User IX
Super User IX

Re: Subtracting two rows

@ehsanbh , The information you have provided is not making the problem clear to me. Can you please explain with an example.

 

There is nothing like the previous row in Power BI. Now if you meter /step /year/week no. Or any incremental no or rank you can use rank or number for the previous row. The condition is that you have to move that to a new table and use that to build current previous

 

example in the case of week(Step/meter). A new column in week/date table

new column

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

 

new measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))


Appreciate your Kudos.




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II
Helper II

Re: Subtract two rows

@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?

Highlighted
Community Support
Community Support

Re: Subtract two rows

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.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors