Forum Discussion
Calculating a difference between two rows as a calculated column
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!
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] )
3 Replies
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] )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?
TomislavNo idea. I didn't have that issue. See attached xlsx.
