How to count number of elements but only when it's the last state

[Anonymous]

Hi everyone, 

I'd like to show a number which is a count but only when the update_date is the latest.

Example :

here is my dataset :

ID	Update_date	Status
1	01/01/2020	1
1	02/01/2020	1
1	03/01/2020	0
2	01/01/2020	0
2	02/01/2020	1
3	01/01/2020	0
3	02/01/2020	0
3	03/01/2020	0
3	04/01/2020	1

I'd like to display the number of each last status but only once per ID, so for the number of "1" status, if the current date is 05/01/2020, I should read "2".

And for the number of "0" status, if the current date is 05/01/2020, I should read "1".

Could you help me please?

Thank you.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create a measure to meet your requirement:

Count of Last Date = 
VAR t =
    FILTERS ( 'Table'[Status] )
RETURN
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( 'Table' ),
                'Table'[ID],
                "LastStatus",
                VAR m =
                    CALCULATE (
                        MAX ( 'Table'[Update_date] ),
                        FILTER (
                            'Table' ,
                            'Table'[Update_date] <= MAX ( 'calendar'[Date] )
                        )
                    )
                RETURN
                    CALCULATE (
                        COUNTROWS ( 'Table' ),
                        FILTER ( 'Table', 'Table'[Update_date] = m && 'Table'[Status] IN t )
                    )
            ),
            [LastStatus] <> 0
        )
    )

Best regards,