Count only last group of specifc value

[Anonymous · Accepted solution]

Hi @EFI ,

Please try below steps:

1. below is my test table

Table:

2. create a new coulmn in Table with below dax formula

Diff Date =
VAR tmp =
    FILTER ( 'Table', [ABS_TYPE_DESCRIPTION] = "300-maladie" )
VAR rk =
    RANKX ( tmp, [Date],, ASC )
VAR _abs = [ABS_TYPE_DESCRIPTION]
VAR val =
    IF ( _abs = "300-maladie", rk, 0 )
VAR _date = [Date]
RETURN
    _date - val

3. create a new table with below dax formula

Table 2 =
VAR tmp =
    FILTER ( 'Table', [ABS_TYPE_DESCRIPTION] = "300-maladie" )
VAR tmp1 =
    SUMMARIZE ( tmp, 'Table'[Diff Date], "Cnt", COUNT ( 'Table'[Date] ) )
RETURN
    tmp1

4. create a measure with below dax formula, and add a card visual with this measure

Latestd consecutive count =
VAR max_date =
    MAXX ( 'Table 2', [Diff Date] )
VAR val =
    CALCULATE ( MAX ( 'Table 2'[Cnt] ), 'Table 2'[Diff Date] = max_date )
RETURN
    val

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.