Forum Discussion

PJ_01's avatar
PJ_01
Frequent Visitor
1 year ago
Solved

Get the most recent status

I have a table which contains the following data:

DateCategoryProgress_Status
1/14/2024x01
1/15/2024x02
1/15/2024x03
1/17/2024x05
1/18/2024x04
1/21/2024x08
1/23/2024x12
1/16/2024y01
1/22/2024y12


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:

Category1/13/20241/14/20241/15/20241/16/20241/17/20241/18/20241/19/20241/20/20241/21/20241/22/20241/23/2024
x 01030305040505080812
y   0101010101011212


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?

Category1/13/20241/14/20241/15/20241/16/20241/17/20241/18/20241/19/20241/20/20241/21/20241/22/20241/23/2024
x 01030305040404080812
y   0101010101011212


Thanks a lot for the help !

Need Help
  • Deku's avatar
    Deku
    1 year ago

    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

                )

            )

5 Replies

    • PJ_01's avatar
      PJ_01
      Frequent Visitor
      1 year ago

      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's avatar
        Deku
        Icon for Super User rankSuper User
        1 year ago

        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

                    )

                )