how to calculate difference between two rows in a table ?

[shilunannan]

Hi, 

Any help is appreciated. I'm creating a dashboard to display pricing info. With selecting two price curves, the corresponding grades are displayed in the table beside it. I'm wondering if there is a way to show the difference between selected two grades?

And here is my sample table. 

EOD Date	Grid Point Month	Price Curve	Price
3/1/2021	3/1/2021	C5-CRW	0.9
3/1/2021	4/1/2021	C5-CRW	1.5
3/1/2021	5/1/2021	C5-CRW	0.7
3/1/2021	6/1/2021	C5-CRW	0.2
3/1/2021	7/1/2021	C5-CRW	-1.5
3/1/2021	8/1/2021	C5-CRW	-1.6
3/1/2021	9/1/2021	C5-CRW	-1.7
3/1/2021	10/1/2021	C5-CRW	-1.2
3/1/2021	11/1/2021	C5-CRW	-1.2
3/1/2021	12/1/2021	C5-CRW	-1.2
3/1/2021	1/1/2022	C5-CRW	-1.75
3/1/2021	2/1/2022	C5-CRW	-1.75
3/1/2021	3/1/2022	C5-CRW	-1.75
3/1/2021	4/1/2022	C5-CRW	-2.75
3/1/2021	5/1/2022	C5-CRW	-2.75
3/1/2021	6/1/2022	C5-CRW	-2.75
3/1/2021	7/1/2022	C5-CRW	-4.1
3/1/2021	8/1/2022	C5-CRW	-4.1
3/1/2021	9/1/2022	C5-CRW	-4.1
3/1/2021	10/1/2022	C5-CRW	-3.6
3/1/2021	11/1/2022	C5-CRW	-3.6
3/1/2021	12/1/2022	C5-CRW	-3.6

Really appreciated! 

[v-kelly-msft · Accepted solution]

Hi @shilunannan ,

Create a measure as below:

Measure = 
IF(ISINSCOPE('Table'[Price Curve]),
SUM('Table'[Price]),
CALCULATE(SUM('Table'[Price]),FILTER(ALLSELECTED('Table'),'Table'[Price Curve]=MAX('Table'[Price Curve])&&'Table'[EOD Date]=MAX('Table'[EOD Date])))-CALCULATE(SUM('Table'[Price]),FILTER(ALLSELECTED('Table'),'Table'[Price Curve]=Min('Table'[Price Curve])&&'Table'[EOD Date]=MAX('Table'[EOD Date]))))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@shilunannan , You can have to create a measure like

new measure =
var _min = minx(allselected(Table), Table[Price Curve])
var _max = maxx(allselected(Table), Table[Price Curve])
return
calculate(Average(Table[Price]), filter(Table, Table[Price Curve] =_max)) -calculate(Average(Table[Price]), filter(Table, Table[Price Curve] =_min))