Forum Discussion
Difference between two entries
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~
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!
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~
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!!