Difference of two rows for same column in matrix visual

[adnanarain]

I am attaching sample data and the desired output. with this data, I am using matrix visual to find number of accounts, Difference, and %. Number of accounts is a simple  count([account Number]) and the difference for row 0 (i.e Date Difference Column) will always be 0 and Row 1 is Account numbers of Row 0 - account numbers of Row 1, in example Account numbers of Row 0 = 3 and Account numbers of Row 1 = 1 so the difference for Row 1 will be 3-1 = 2 and last I want to calculate % for Row 0 the % will be 0 and for Row 1 the formula for % is difference (i.e 2 for Row 1) divided by count of account number for Row 0 (i.e 3) so 2/3 = 67%.

Below is the sample data:

Date Difference	Account Number	Account Open date
0	1	2013
0	2	2013
0	3	2014
0	2	2013
0	3	2014
0	3	2014
1	4	2014
1	5	2013
1	6	2014
2	7	2013
2	8	2014
2	9	2013
3	10	2013
3	11	2014
4	12	2013
4	13	2013
4	14	2014
4	15	2014

and below is the desired output, I tried to show for Year 2013 only:

	2013
Date Difference	Count of account	Difference	%
0	3	0	0%
1	1	2	67%
2	2	-1	-100%
3	1	1	50%
4	2	-1	-100%

I tried to explain, apologies in advance if anything is not clear.

[harshnathani · Accepted solution]

HI @adnanarain ,

Create these measures

Count of Accounts = Count('Table'[Account Number])

Difference = 
var a = CALCULATE([Count of Accounts] , FILTER(ALL('Table'),'Table'[Account Open date] = MAX('Table'[Account Open date]) && 'Table'[Date Difference] = MAX('Table'[Date Difference]) - 1))

RETURN

IF (ISBLANK(a), 0, a - [Count of Accounts])

Percentage = 
var a = CALCULATE([Count of Accounts] , FILTER(ALL('Table'),'Table'[Account Open date] = MAX('Table'[Account Open date]) && 'Table'[Date Difference] = MAX('Table'[Date Difference]) - 1))
RETURN
DIVIDE([Difference],a,0)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

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