subtracting previous row value from current row dynamically in Table visual

[aatish178]

@quantumudit Hi Udit,I have a below dataset:

FY	StudentID	StudentName	Gender	Status	ClassDivision
FY19	11	palash	M	Absent	DIV A
FY19	12	akash	M	Absent	DIV A
FY19	13	vikas	M	Absent	DIV A
FY19	14	subhas	M	Absent	DIV A
FY19	15	kailas	M	Absent	DIV B
FY19	16	suresh	M	Absent	DIV B
FY19	17	ramesh	M	Absent	DIV B
FY19	18	ankesh	M	Present	DIV B
FY19	19	mahesh	M	Present	DIV B
FY19	20	priti	F	Present	DIV B
FY19	21	dipali	F	Present	DIV B
FY20	22	abcd	F	Present	DIV B
FY20	23	swati	F	Present	DIV B
FY20	24	vaishnavi	F	Present	DIV B
FY20	25	akanksha	F	Present	DIV B
FY20	26	pallavi	F	Present	DIV B
FY20	27	rama	F	Present	DIV B
FY20	28	radha	F	Present	DIV B
FY20	29	sumedh	M	Admission Can	NA
FY20	30	shailesh	M	Admission Can	NA
FY20	31	venkat	M	Admission Can	NA
FY20	32	pandi	M	Admission Can	NA
FY20	33	reddeppa	M	Admission Can	NA
FY20	34	vishnu	M	Admission Can	NA
FY20	35	ravi	M	Admission Can	NA
FY20	36	kiran	F	Admission Can	NA
FY20	37	anupama	F	Admission Can	NA
FY20	38	rehan	M	On leave	DIV B
FY20	39	rijwan	M	On leave	DIV B
FY20	40	akbar	M	On leave	DIV C
FY20	41	atli	M	On leave	DIV C
FY20	42	vetri	M	On leave	DIV C
FY21	43	thomas	M	Waiting	Yet to update
FY21	44	peter	M	Waiting	Yet to update
FY21	45	fredrik	M	Waiting	Yet to update
FY21	46	venkatesh	M	Waiting	Yet to update
FY21	47	dinesh	M	Waiting	Yet to update
FY21	48	chaitanya	M	Waiting	Yet to update
FY21	49	rocky	M	Waiting	Yet to update

once I loaded this data in PBI then I want to get below output where difference column is actually a difference of count of studentID for each FY: Scenario 1:

Scenario 2:

Power BI Result:

Here I am suspecting since I have calculated the difference as a calculated column the result is static., I want your help to make it dynamic., I guess with measure that can be possible. can you please help me with measure expression?

Kind Regards,

Aatish

[Ashish_Mathur]

Hi,

Do you have a proper Date column?  If no, then do you have a Year and Month column?  Also, from which month does every FY start?

[v-jialongy-msft · Accepted solution]

Hi @aatish178 

First, we need to ensure we have a measure that calculates the total count of StudentID for each FY.

Total Students = COUNT('Table'[StudentID])

Next, we'll create a measure that calculates the difference in the count of StudentID between each FY and the previous FY.

Difference = 
VAR CurrentFY = MAX('Table'[FY])
VAR PreviousFY = CurrentFY - 1
VAR CurrentFYCount = CALCULATE([Total Students], 'Table'[FY] = CurrentFY)
VAR PreviousFYCount = CALCULATE([Total Students], 'Table'[FY] = PreviousFY)
RETURN CurrentFYCount - PreviousFYCount

% = 
VAR CurrentFY = MAX('Table'[FY])
VAR PreviousFY = CurrentFY - 1
VAR CurrentFYCount = CALCULATE([Total Students], 'Table'[FY] = CurrentFY)
VAR PreviousFYCount = CALCULATE([Total Students], 'Table'[FY] = PreviousFY)
RETURN (CurrentFYCount - PreviousFYCount)/PreviousFYCount

This is the result you want:

Best Regards,

Jayleny

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

[aatish178]

Hi Jayleny,

Thanks the solution is working as expected. The only change I did is I used Right(Max(FY),2) to fetch only number for further subtraction and it worked.

Thanks once again.