Calculate Difference between two rows

[Anonymous]

Sample Data     Desired Output      

Hi,

I am trying to a add calculated column  that will return the difference of two rows in column: Value. by Date, Retailer and Attribute.

Sample data attached is for one Retailer and Attribute, In this case the output shouls look like the extreme right column(Desired Output)

I tried everything available on web without any success

[v-yingjl]

Hi @Anonymous ,

To get the previous row value, you need to create an index column in power query editor first:

Create this calculated column:

Anomoly =
VAR _date = [Date]
VAR _index = [Index]
VAR lastrow =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = _date
                && 'Table'[Index]
                    = CALCULATE (
                        MAX ( 'Table'[Index] ),
                        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _date && 'Table'[Index] < _index )
                    )
        )
    )
RETURN
    [Value] - lastrow

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

Best Regards,
Yingjie Li

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

[Anonymous]

Hi @v-yingjl , 

Thanks for so much for your solution, but this will not work if i had different Retailers and Attributes.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

If you want to calculate for each Retailer and Attribute, just add their fields as variables into the previous formula:

Anomoly =
VAR _date = [Date]
VAR _index = [Index]
VAR _re = [Retailer]
VAR _att = [Attribute]
VAR lastrow =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = _date
                && 'Table'[Retailer] = _re
                && 'Table'[Attribute] = _att
                && 'Table'[Index]
                    = CALCULATE (
                        MAX ( 'Table'[Index] ),
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Date] = _date
                                && 'Table'[Attribute] = _att
                                && 'Table'[Retailer] = [Retailer]
                                && 'Table'[Index] < _index
                        )
                    )
        )
    )
RETURN
    [Value] - lastrow

Attached the modified sample file in the below.

Best Regards,
Yingjie Li

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

[PaulDBrown]

@Anonymous 

If you need this to be dynamic (you are going to be using slicers for example) you should be writing measures instead of calculated columns. RANKX is your friend.
If you get stuck, please provide sample data.

[richbenmintz]

Hi @Anonymous,

Couple Questions:

1. Can you provide the data in a table rather than an image?

2. What is the tie breaker when ordering the rows?

Thanks,

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

@richbenmintz Retailer and Attrbute are tie breaker

I am unable to add Table, give me HTML error ans moves the reply to spam

[richbenmintz]

Hi @Anonymous,

- you should just be able to paste the data into the body of the message

- within the retailer and attribute how are the rows supposed to be ordered, in your sample data the retailer and attribute are duplicated how would the system know which row comes first? the natural order of the data or is there another sort by field?