Showing latest record in matrix with date columns

[ABech]

Hi geniuses

I have a problem with displaying the latest record (not numerical value) in a matrix with week numbers in the columns.

The matrix is used to show employees' workload status during a week. Status is indicated, for example, with the letters A, B, C, D and E, where A is the least busy and E is the busiest.

My challenge is that employees can enter new values during the week, if their status changes, and the status can even change the same day, which is why I need to always show the most recently entered value in my matrix.

I've tried Amit Chandak's example here:
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

Last Status = var _max = maxx(filter(ALLSELECTED(Data), Data[Name] = Max(Data[Name])), Data[Date]) 
return 
CALCULATE(max(Data[Rating]), filter((Data) ,  Data[Date] =_max))

The DAX example here correctly shows the latest entry per employee in other types of visualizations, but in my matrix with week numbers in the columns it only shows the latest status in the last week, while all previous weeks are blank.

A simplified data set looks like this.

Name	Rating	Date (dd-mm-yyyy hh:mm:ss)	Week
Alan	B	14-03-2023 08:24:15	11
Adam	D	14-03-2023 09:56:32	11
Anna	C	14-03-2023 10:50:32	11
Anna	A	14-03-2023 12:15:25	11
Adam	C	15-03-2023 09:34:12	11
Anna	A	21-03-2023 08:25:01	12
Adam	C	21-03-2023 11:54:24	12
Anna	D	22-03-2023 09:05:02	12

With Amit Chandak's formula, I get the following result in my Matrix Visualization:

Name	Week 11	Week 12
Alan	B
Adam		C
Anna		D

What I want to achieve is this:

Name	Week 11	Week 12
Alan	B
Adam	C	C
Anna	A	D

Bonus info: I'm using SQLBI's date template, so I do have a well populated date table.

I hope you can help me.

[v-binbinyu-msft · Accepted solution]

Hi @ABech ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

Measure =
VAR cur_name =
    SELECTEDVALUE ( 'Table'[Name] )
VAR cur_week =
    SELECTEDVALUE ( 'Table'[Week] )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), [Name] = cur_name && [Week] = cur_week )
VAR max_date =
    MAXX ( tmp1, [Date (dd-mm-yyyy hh:mm:ss)] )
VAR _val =
    CALCULATE (
        MAX ( [Rating ] ),
        FILTER ( tmp1, [Date (dd-mm-yyyy hh:mm:ss)] = max_date )
    )
RETURN
    _val

3. add a matrix visual with fields and measure

Please refer the attached .pbix file. 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ABech]

Thank you so much and thank you for the time you've spend! That seems to work perfectly, and I can safely say, that i would not have come to that conclusion by my self. 🙂