Calculating a difference between two rows as a calculated column

[tomislav_mi]

Hey guys,

Could you please help me out to get the difference between rows as a calculated column considering a few parameters?

I would need to calculate the difference between sales, having in mind that I need to do it for each Account group separately. I was trying to do it with EARLIER, PREVIOUSMONTH, PARARALEPERIOD but always stopped somewhere in the process. 

This is an example of the columns and desired outcome.

Account	Date	Sales	Calculated column
A	9/1/2012	120
A	10/1/2012	150	30
A	11/1/2012	150	0
A	12/1/2012	100	-50
B	9/1/2012	10
B	10/1/2012	20	10
B	11/1/2012	30	10
B	12/1/2012	40	10

Thank you!

[Greg_Deckler · Accepted solution]

Probably something like below. PBIX attached.

Column = 
    VAR __Previous = 
        MAXX(
            FILTER(
                ALL('Table'),
                [Account] = EARLIER([Account]) &&
                    [Date] < EARLIER([Date])
            ),
            [Date]
        )
RETURN
    [Sales] - 
    MAXX(
        FILTER(
            ALL('Table'),
            [Account] = EARLIER([Account]) &&
            [Date] = __Previous
        ),
        [Sales]
    )

[tomislav_mi]

Thank you @Greg_Deckler .

Works perfectly in PowerBI. I also use same Excel data model and when I try to use the same approach in Power Pivot it doesn't work there. There it calculates the difference only for the first row?

Do you have any idea why this could be the case? 

Tomislav

[Greg_Deckler]

No idea. I didn't have that issue. See attached xlsx.