Get lastest score and difference from previous

deaconb — Fri, 26 Apr 2024 17:49:09 GMT

We have training data that looks like below:

TrainingID	Name	ResponseID	Score	Latest Score	Difference from Previous Score
123	A	7125417	1	2	-
123	A	7125423	2	2	+1
123	A	7125461	3	2	+1
123	A	7125465	2	2	-1
234	B	7125955	1	3	-
234	B	7125962	2	3	+1
234	B	7125978	3	3	+1
234	B	7125985	3	3	0
345	A	7125997	1	4	-
345	A	7134183	2	4	+1
345	A	7134191	3	4	+1
345	A	7134196	4	4	+1
345	B	7134204	1	3	-
345	B	7134254	2	3	+1
345	B	7134267	3	3	+1

A "Name" can complete a given "TrainingID" however many times they want. Each time they compelete a TrainingID, they get a "Score". The training event is stored as a transaction in the database by "RecordID"; record IDs increment up 1 for each training event, so it can be used to order things chronologically 1-N, where item 1 was completed before item 2, etc.

The first event of a given TrainingID for a Name, I don't need to return a value. All other events, I need to return the following: Latest Score (red) column and Difference from Previous Score (blue) column. I don't necessarily need the "+" in front of the positive increments, but I definitely need a negative value where they got a higher score the previous event than the lastest event.

I've got the Latest Score (red) column working by using a Group By function. Where I'm stuck is getting the Difference from Previous Score (blue) column.

I've tried creating a measure with the following, but it's not returning the values based on the Training ID and Name, just the Training ID.

SELECTCOLUMNS(

INDEX(

-1,

ALLSELECTED(Query1[ResponseID], Query1[TrainingID], Query1[Score], Query1[Name]),

ORDERBY(Query1[ResponseID]),

PARTITIONBY(Query1[TrainingID], Query1[Name])

[Score])

Any help or guidance with the final column I need is greatly appreciated!!

Re: Get lastest score and difference from previous

lbendlin — Sat, 27 Apr 2024 00:56:03 GMT

Very much the same formula, just use OFFSET instead of INDEX.

Latest Score = MAXX(INDEX(-1,ALL(Query1),PARTITIONBY([TrainingID],[Name]),MATCHBY([ResponseID])),[Score]) Difference = var a = MAXX(OFFSET(-1,ALL(Query1),PARTITIONBY([TrainingID],[Name]),MATCHBY([ResponseID])),[Score]) return if(not ISBLANK(a),[Score]-a)

Re: Get lastest score and difference from previous

deaconb — Mon, 29 Apr 2024 12:58:12 GMT

Yes - that was it! Thank you SO much! I don't know why I didn't think to use OFFSET!

topic Re: Get lastest score and difference from previous in DAX Commands and Tips

Get lastest score and difference from previous

Re: Get lastest score and difference from previous

Re: Get lastest score and difference from previous