Filter table to get the last value per user

[FooF0x]

Hi,

I would like to calculate the score of someone based on its results compared to the results of the other users. I'm currently stuck when I filter the table. Let me explain.

I have users and categories. Each user has a result per category and the date when he got his result.

I would like to get, for each row, the score which is the result compared to the previous ones.

The range of values to take for 1 row (so for 1 manager in 1 category at a certain date) is the last result of each user for this category before the date of the row.

Getting all the results for a single category before the date is fairly easy but I can't find a way to only take the last results of each user in this range.

VAR PreviousScores= FILTER( 'Results' , 

    'Results'[CatID] = EARLIER( 'Results'[CatID] )

    && 'Results'[Date] <= EARLIER( 'Results'[Date] )

)

This gives me the results for the category and before the date of the current row.

But within those rows, I only need the last results for each user. This is where I'm stuck.

Row	Username	Date	Result
2	User 1	10/01/2020	30
3	User 1	15/10/2019	20
4	User 1	10/09/2019	24
5	User 2	10/01/2020	50
6	User 2	15/10/2019	45
7	User 2	10/09/2019	33
8	User 3	10/01/2020	10
9	User 3	15/10/2019	22
10	User 3	10/09/2019	35
11	User 4	4/01/2020	56
12	User 4	5/10/2019	34
13	User 4	2/09/2019	23

For example, in this scenario, I have the results for 1 category. To get the scores, here are the values I should use:

• For row 2: use 2, 5, 8, 11
• For the first row (10/01/2020), we take the last results of each user on this date. We can see that the dates can be different.
• The last result of User 4 is on the 4th of Jan so this is the one I use here.
• Row 3: 3, 6, 9, 12
• Row 4: 4, 7, 10, 13
• ...
• Row 11: 3, 6, 9, 11
• This result is on the 04/01/2020 so we need the last results of the other user before the 04/01/2020.
• Row 12: 4, 7, 10, 13

What would be the best way to filter these rows in my DAX function for the calculated column?

Thank you very much for your help

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.

My Recent Blog -
Time Intelligence - Direct Query

[FooF0x]

Hi,

Download the file 

Sorry for the delay, here a sample of the data. In the table, I added a calculated column. This column only returns the number of rows to consider. This number should, in this case, always be 4 or 3 for the very first ones.

In any case, max 4 as there are only 4 users and I always need the last score for each of them.

Thank you very much for your help,

[FooF0x]

Hi,

I'll try to give more explanations. I'll provide a pbix file as soon as I can.

So I have a table with all the results for every user in every category. One user can have multiple results for the same category. He got them at different dates.

So my table has the following column: UserID, CategoryID, Date and Result.

For every row, I need to compute a score. This score is based on all the latest results of each user previous to this one.

So I'm adding a calculated column to do this. In it, I'm filtering this table to only get the results that I need and this is where I'm stuck, I cannot find a way to filter the table to get what I need.

So my first step was to get all the results previous to the row.

VAR PreviousResults = FILTER( 'Results' ,
'Results'[CategoryID] = EARLIER( 'Results'[CategoryID] )
&& 'Results'[Date] <= EARLIER( 'Results'[Date] )
)

But it's not finished quite yet because if User 1 has 3 results for this category before the row, I want to only get the latest because all the older ones are irelevant. This is what I say when I write "All the latest results of each user before the row".

I'm stuck at this second step.

Considering the following table is the results for 1 category:

Username	Date	Result
User 1	1/01/2020	30
User 1	1/12/2019	40
User 1	1/11/2019	33
User 1	1/10/2019	45
User 1	1/09/2019	22
User 2	1/01/2020	35
User 2	1/12/2019	23
User 2	1/11/2019	65
User 2	1/10/2019	10
User 2	1/09/2019	30
User 3	2/01/2020	45
User 3	2/12/2019	60
User 3	2/11/2019	50
User 3	2/10/2019	24
User 3	2/09/2019	60

In my new column, for the first row which is User 1 on the 01/01/2020, I need the latest scores of all the user on the 01/01/2020. So I'll get User 1 on the 01/01/2020, User 2 on the 01/01/2020 and User 3 on the 02/12/2019 (This is its 1st score past to 01/01/2020).

For User 1 on the 01/12/2019, I need User 1 on the 01/12/2019, User 2 on the 01/12/2019 and User 3 on the 02/11/2019.

For User 3 on the 02/01/2020, I need User 1 on the 01/01/2020, User 2 on the 01/01/2020 and User 3 on the 02/01/2020.

For User 3 on the 02/12/2019, I need User 1 on the 01/12/2019, User 2 on the 01/12/2019 and User 3 on the 02/12/2019.

I hope this makes more sense. Let me know if I need to provide more explanations.