Forum Discussion

Krcmajster's avatar
Krcmajster
Helper IV
4 years ago
Solved

Count only values with most recent date

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 n...
Need Help
  • Krcmajster's avatar
    Krcmajster
    4 years ago

    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's avatar
    Anonymous
    4 years ago

    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

amitchandak's avatar
amitchandak
Super User
4 years ago

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's avatar
Krcmajster
Helper IV
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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