Differences between first row and other rows along with the date slicers. Need DAX measure trick

[Anonymous]

 Hi Folks,

I am trying to get the difference between first rows and others along with the date slicer and other filters. I have some categories and date filter. So, whenever user selects date and the categories the table shows the filter data and I want to compute the differences between the first row of the filtered data and rest rows of the filtered data without combining the computation for both/multiple categories. Here is the table that I'm expecting. I am trying to create one measure but it is not working. 

Please suggest me the way. Thanks 

READY COLUMN	To Calculate the NEW Columns
Index	Fund	Return	Price	Date	Start (Sno = 3)
1	A	0.08	108	1/1/2019
2	A	-0.06	101.52	1/2/2019
3	A	0.06	107.6112	1/3/2019
4	A	-0.1	96.85008	1/4/2019	-0.1
5	A	0.03	99.7555824	1/5/2019	-0.073
6	A	0.1	109.7311406	1/6/2019	0.0197
7	D	0.03	103	1/1/2019
8	D	-0.04	98.88	1/2/2019
9	D	0.02	100.8576	1/3/2019
10	D	-0.02	98.840448	1/4/2019	-0.040384
11	D	-0.09	89.94480768	1/5/2019	-0.12674944
12	D	0.04	93.54259999	1/6/2019	-0.091819418
13	D	-0.08	86.05919199	1/7/2019	-0.164473864
14	D	-0.07	80.03504855	1/8/2019	-0.222960694

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

If i understand you correctly, ALLEXCEPT() function might be helpful for you.

Please refer to measure below and see if the result achieve your expectation:

Measure 3 = 
var a = CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Fund]))
var b = CALCULATE(MAX('Table'[Price]),FILTER(ALLEXCEPT('Table','Table'[Fund]),'Table'[Index] = a))
return
IF(MAX('Table'[Index])=a,BLANK(),MAX('Table'[Price])-b)

BTW, pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Thanks for your help. 

 It works for my dashboard. But when i'm trying to use the date slicer and other filters it is still taking the first value only. e.g if I choose date slicer from 01/03/2019 the current query is still considering the 01/01/2019 as base date and changing it to the 01/03/2019. Could you please help me in this? THnaks