Forum Discussion
subtracting previous row value from current row dynamically in Table visual
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
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)/PreviousFYCountThis 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.
3 Replies
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)/PreviousFYCountThis 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.
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.
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?
