- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
02-22-2024 10:35 PM | |||
11-04-2018 11:16 PM | |||
06-05-2024 08:15 PM | |||
07-09-2024 12:42 AM | |||
06-28-2024 05:00 AM |
User | Count |
---|---|
115 | |
75 | |
45 | |
44 | |
32 |
User | Count |
---|---|
172 | |
90 | |
66 | |
46 | |
45 |