Difference between two entries

[Theo_Alex]

Hi ya’ll,

I have a problem regarding my data model and its calculation. I have a data model which is organized in rows. Quite similar to an OLAP/Cube.

Each User and Entry gets an ID. Also each score has its own id. The score in this particular example has its range from 0 to 9. Each Score Id represents a specific score description. For example, Score for Management. Score for Executives and so on.

All scores can be summed up to get the average score.

Now my problem is that I do not know how to calculate the difference between the score of the latest entry and the entry before. If it is even possible to calculate the difference between the last and and any entry before would be even more awesome but not necessary.

What I was able to do is to calculate the last entry out of this data:

Last Entry =

var _Max = maxx(filter(allselected(Table),Table[Entry_Id]=max(Table[Entry_ID])),Table[Entry_Date])

Return

Calculate(sum(Table[Score]),filter(Table,Table[Entry_Date]=_Max))

I really can’t find a proper solution for this problem.

So for instance for User_id = 1

The Delta of Score_ID 1 would be 2 – 4 = -2

The Delta of Score_ID 2 would be 0 – 5 = -5

Thanks a lot in advance!!

User_ID	Entry_ID	Entry_Date	Score	Score_ID
1	1	01.01.2024	4	1
1	1	01.01.2024	5	2
1	1	01.01.2024	2	3
1	1	01.01.2024	3	4
2	3	03.01.2024	9	1
2	3	03.01.2024	9	2
2	3	03.01.2024	3	3
2	3	03.01.2024	1	4
3	2	02.01.2024	1	1
3	2	02.01.2024	5	2
3	2	02.01.2024	8	3
3	2	02.01.2024	6	4
1	4	04.01.2024	2	1
1	4	04.01.2024	0	2
1	4	04.01.2024	3	3
1	4	04.01.2024	9	4
2	5	05.01.2024	8	1
2	5	05.01.2024	6	2
2	5	05.01.2024	0	3
2	5	05.01.2024	3	4

[Anonymous]

Hi @Theo_Alex ,

First of all, many thanks to @gmsamborn  for your very quick and effective replies.

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate score for last entry.

Last Entry = 
var entr_i = MAX('Table'[Entry_ID])
var _Max = CALCULATE(MAX('Table'[User_ID]), FILTER('Table', 'Table'[Entry_ID] = entr_i))
Return
Calculate(SUM('Table'[Score]),filter(ALLEXCEPT('Table', 'Table'[Score_ID]), 'Table'[User_ID] = _Max  && 'Table'[Entry_ID] > entr_i ))

3.Create the new measure to the difference between two entries.

Difference two entres = IF(ISBLANK([Last Entry]), BLANK(), [Last Entry] - SUMX('Table', 'Table'[Score]))

4.Drag two measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[Theo_Alex]

Hi!
thanks for taking your precious time to help me out, I really appreciate it. I implented as you mentioned but I guess I described it wrongly since I don't get the desired values I am expecting. I attached a picture for a better understanding.

I pre filtered all data for User_ID = 5. So we need to keep in mind that here a several more User_IDs in this table. FK stands for Foreign Key.

Now I have several Entries (4 in Total: Entry_ID [5,6,8,10]). Entry with Entry_ID = 10 (May 14th 2024) is the newest / Last Entrey. Entry with Entry_ID = 5 (April 23rd 2024) is the oldest / first entry this userhas made. So for the user's first Entry there can't be any difference since it is its first Entry the is no score before. 

Now I want to calculate the difference for each current Entry and its score. For instance for Score_ID = IP for the Current Entry_ID = 10 would be 9 - 3 since the entry before was on May 9th with Entry_ID = 8. For Score_ID = MS would be 3 - 3 = 0 and so on.

The Difference of Score_ID = IP for the Entry_ID = 8 (May 9th 2024) would be the difference between score from Entry_ID = 8 (May 9th 2024) and Entry_ID = 5 (April 24th 2024) and so on. In this Case 3-3 = 0.

So we always have a current Score we are looking at and want to deduct the Entry before for each Score_ID and User_ID.

I hope I was able to express myself better now.

Thanks again in advance!

[xifeng_L · Accepted solution]

Hi @Theo_Alex ,

You can try below measure.

Last Entry = 
CALCULATE(
    SUM('Table'[Score]),
    OFFSET(
        -1,
        ALL('Table'[User_ID],'Table'[Score_ID],'Table'[Entry_ID],'Table'[Entry_Date]),
        ORDERBY('Table'[Entry_Date],ASC),
        PARTITIONBY('Table'[User_ID],'Table'[Score_ID])
    )
)

Demo - 5-31calculate Difference between two entries.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[Theo_Alex]

It worked out! I Had to change the format of the date in the database before I load it into my model but since I changed the format so PBI could recognize it as date I could calculate the differences between all entries per User, per Score, per Entry. And the first Entry has of course no data. Nice Thanks!!

[gmsamborn]

Hi @Theo_Alex 

Would the following measures help?

Score = SUM( 'FactTable'[Score] )

Prev = 
    CALCULATE(
        [Score],
        OFFSET(
            -1,
            ORDERBY( 'FactTable'[Entry_Date], ASC ),
            PARTITIONBY( FactTable[User_ID] )
        )
    )

Diff = [Score] - [Prev]

Let me know if you have any questions.

Difference between 2 entries.pbix

[xifeng_L]

Hi @Theo_Alex 

Can you be more specific about the examples of computational logic, such as 2-4 = -2, where the 2 is obtained and the 4 is obtained?

---

So for instance for User_id = 1

The Delta of Score_ID 1 would be 2 – 4 = -2

The Delta of Score_ID 2 would be 0 – 5 = -5

And what is your expected result, a new calculated column or using a matrix to display it? If a matrix, what are the dimensions?