Get the most recent status

[PJ_01]

I have a table which contains the following data:

Date	Category	Progress_Status
1/14/2024	x	01
1/15/2024	x	02
1/15/2024	x	03
1/17/2024	x	05
1/18/2024	x	04
1/21/2024	x	08
1/23/2024	x	12
1/16/2024	y	01
1/22/2024	y	12

I would like to create a matrix visual which shows the latest Progress_Status for every day I have created the following dax measure:

var lastDate=MAX(Calendar[Date])
var lastProgressStatus=MAX(Fact[Progress_Status])
var lastProgressStatusDate=
 CALCULATE(
MAX(Calendar[Date]),
Calendar[Date]<= lastDate,
Fact[Progress_Status] <> BLANK()
)
RETURN
IF(lastProgressStatus==BLANK() || lastProgressStatus =="",
 CALCULATE(
MAX(Fact[Progress_Status]),
Calendar[Date]<= lastProgressStatusDate
),
lastProgressStatus
)

This gives me the following result:

Category	1/13/2024	1/14/2024	1/15/2024	1/16/2024	1/17/2024	1/18/2024	1/19/2024	1/20/2024	1/21/2024	1/22/2024	1/23/2024
x		01	03	03	05	04	05	05	08	08	12
y				01	01	01	01	01	01	12	12

However this is not the result i want, because in some cases the statuses are not in ascending order. How do i change my formula to get this result instead?

Category	1/13/2024	1/14/2024	1/15/2024	1/16/2024	1/17/2024	1/18/2024	1/19/2024	1/20/2024	1/21/2024	1/22/2024	1/23/2024
x		01	03	03	05	04	04	04	08	08	12
y				01	01	01	01	01	01	12	12

Thanks a lot for the help !

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[PJ_01]

Thanks, this was great help.

[Deku]

Please see this blog post

[PJ_01]

Thanks, this looks like what I am looking for, however I am not getting the same result as in the blog:
I have downloaded the file from the there and this is what I am getting:

And this is what it should look like according to the blog:

It looks like the REMOVEFILTERS function is not working.

[Deku · Accepted solution]

That is the step one measure, which steps towards the solution. At the end of the blog there are two other version

This was one of them

// Using TOPN

VAR currentState = SELECTEDVALUE(States[State])

VAR currentDate = SELECTEDVALUE('Calendar'[Date])+1

RETURN

    COUNTROWS(

        FILTER(

            ALL( data[TestID] ),

            SELECTCOLUMNS(

                TOPN(

                    1,

                    CALCULATETABLE( 

                        FILTER( 

                            'data', 

                            [DateTime] < currentDate

                        )

                        ,REMOVEFILTERS('Calendar'[Date])

                        ,REMOVEFILTERS('States'[State])

                        ),

                    'data'[DateTime], 

                    DESC

                ),

                "Last Value", [State]

                )

            = currentState

            )

        )