Count only values with most recent date

[Krcmajster]

I am trying to get the count for pie chart where only latest values for specific account should be counted. I can get them but with creating table, however I want to do it with DAX only so I do not need to store the table. 

I was trying to use summarize, but I can't manage to use columns from virtual table to filter table

Health	Date	Account
Green	01 April 2022	Account1
Red	01 March 2022	Account1
Green	01 April 2022	Account2
Yellow	01 May 2022	Account2
Red	01 February 2022	Account3

DAXOnlyCount = 
var vTable =
SUMMARIZE ( ALL ( 'Table' ), 'Table'[Account], "Latest", MAX ( 'Table'[Date] ) )


return
CALCULATE(count('Table'[Health]),'Table'[Account]=vTable /*[Account] column*/,'Table'[Date]=vTable /*[Latest] column*/) //I get this is incorrect, I am just not sure how to do it. ))

Desired Output:

[Krcmajster · Accepted solution]

Thanks, but this only returns the latest value overall. I need latest value by each account. 
In case of Account1 that is Green, as date for it is Apr 01, for Account2 it is Yellow since it's on May 01 and for account 3 it is Red, only one value. 

[amitchandak]

@Krcmajster , try if one of the two can help

DAXOnlyCount =
var vTable =
Maxx ( ALL ( 'Table' ), 'Table'[Date] )
return
CALCULATE(count('Table'[Health]), 'Table'[Date]=vTable /*[Latest] column*/)

or

DAXOnlyCount =
var vTable =
Maxx ( ALLexcept ( 'Table','Table'[Account] ), 'Table'[Date] )
return
CALCULATE(count('Table'[Health]), 'Table'[Date]=vTable /*[Latest] column*/)

[Krcmajster · Accepted solution]

Thanks, but this only returns the latest value overall. I need latest value by each account. 
In case of Account1 that is Green, as date for it is Apr 01, for Account2 it is Yellow since it's on May 01 and for account 3 it is Red, only one value. 

[Anonymous · Accepted solution]

Hi @Krcmajster ,

You can create a measure as below to get it, please find the details in the attachment.

Count with most recent date = 
VAR _latestdate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Account] ) )
RETURN 
    COUNTX (
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Account] ), 'Table'[Date] = _latestdate ),
        [Health]
    )

Best Regards