cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dphillips Member
Member

Change in value from one row to the next

I have a column called Time Period which is a string. I have a list of merit rankings for a student which is a whole number. How can I get a simple column which gives me the difference from one time period to the next as shown below? I have looked at some solutions that use an index column but I don't have this. As I said - Time Period is a string, not a date.

 

This data comes from a table where I have used a slicer to filter for a particular student id. I want to see the changes in a particular student's merit ranking over periods of time. Please see below for a link to all the data in the table if this is needed

 

Time PeriodMerit RankingDiff
2015 (T2)47 
2015 (T4)514
2016 (T2)35-16
2016 (T3)449
2016 (T4)473
2017 (T2)7932
2017 (T3)42-37
2017 (T4)508
2018 (T2)25-25

 

Link to full data - Change in Merit Ranking over time.xlsx

 

Any help would be appreciated. Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
v-danhe-msft Super Contributor
Super Contributor

Re: Change in value from one row to the next

Hi @dphillips,

I have modified the Measure, you could refer to below measure:

Measure = RANKX(ALLEXCEPT('Max of Merit Ranking by Time Pe','Max of Merit Ranking by Time Pe'[studentid]),FIRSTNONBLANK('Max of Merit Ranking by Time Pe'[Time Period],'Max of Merit Ranking by Time Pe'[Time Period]),,ASC,Dense)

Result:

1.PNG

You could download the pbix file to have a test.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Re: Change in value from one row to the next

Hi dphillips,
You should to use Power Query in order to order your data.
First by the "studentid" column and second by the "Time Period" column.
Later creat an "index" column.
Finally, creat a measure as show it in image I've attached.
Tell us if works for you.

 

Change in value from one row to the next.png

v-danhe-msft Super Contributor
Super Contributor

Re: Change in value from one row to the next

Hi @dphillips,

Based on my test, you could refer to below measures:

Measure = RANKX(ALL('Max of Merit Ranking by Time Pe'),FIRSTNONBLANK('Max of Merit Ranking by Time Pe'[Time Period],'Max of Merit Ranking by Time Pe'[Time Period]),,ASC,Dense)
Measure 2 = var a=[Measure]-1 
return CALCULATE(SUM('Max of Merit Ranking by Time Pe'[Merit Ranking]))-CALCULATE(SUM('Max of Merit Ranking by Time Pe'[Merit Ranking]),FILTER(ALLEXCEPT('Max of Merit Ranking by Time Pe','Max of Merit Ranking by Time Pe'[studentid]),'Max of Merit Ranking by Time Pe'[Measure]=a))

Result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dphillips Member
Member

Re: Change in value from one row to the next

Hi @v-danhe-msft. Looking at your Measure 2 it looks like this will not work. Note where you skip a rank, then measure 2 has a value of 92 which is clearly not the difference between 91 and 92. This obvioulsy has something to do with the fact that there are gaps from 2016 (T2) to 2016 (T4). Is there any way of fixing this up at all?

v-danhe-msft Super Contributor
Super Contributor

Re: Change in value from one row to the next

Hi @dphillips,

I have modified the Measure, you could refer to below measure:

Measure = RANKX(ALLEXCEPT('Max of Merit Ranking by Time Pe','Max of Merit Ranking by Time Pe'[studentid]),FIRSTNONBLANK('Max of Merit Ranking by Time Pe'[Time Period],'Max of Merit Ranking by Time Pe'[Time Period]),,ASC,Dense)

Result:

1.PNG

You could download the pbix file to have a test.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

dphillips Member
Member

Re: Change in value from one row to the next

That seems to work well @v-danhe-msft. Just one final thing - is it possible to remove the first value eg 91 for measure 2 as I would only like to see the change and not the actual value for the first time period. Is there a way of making this blank? Thanks so much for your help so far.

dphillips Member
Member

Re: Change in value from one row to the next

@v-danhe-msft. I adjust things slightly to remove the first row for Measure 2 which was the actual result, not the difference.

 

Measure 2 = var a=[Measure]-1
return IF([Measure] = 2,BLANK(),CALCULATE(SUM('Results'[Merit Ranking]))-CALCULATE(SUM('Results'[Merit Ranking]),FILTER(ALLEXCEPT('Results','Results'[studentid]),'Results'[Measure]=a)))

 

Things seems to work as I wanted it to now. Thanks so much for your help

dphillips Member
Member

Re: Change in value from one row to the next

@v-danhe-msft Spoke too soon. The data I gave you did not have a number of other columns in it. When I use the measure with all the other data, it is very slow to calculate and comes up with the wrong data. Is there any way to take into account and filter out other columns?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 229 members 2,353 guests
Please welcome our newest community members: