Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Status Tracking

Hello everyone, I have the following table;   ID               Date          Status 1 05/05/2023 A 1 06/05/2023 B 2 01/02/2023 A 2 01/04/2023 C 3 05/05/2023 A 3 06/05/...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi Anonymous 
    Dax formula for your first request :

    Distinct Count A To B =
    CALCULATE(
        DISTINCTCOUNT('Table'[ID]),
        FILTER(
            'Table',
            'Table'[Status] = "B" &&
            CALCULATE(
                MAX('Table'[Date]),
                FILTER(
                    'Table',
                    'Table'[ID] = EARLIER('Table'[ID]) &&
                    'Table'[Status] = "A"
                )
            ) <'Table'[Date]
        )
    )
     
    For Second :
    Distinct Count A To C =
    CALCULATE(
        DISTINCTCOUNT('Table'[ID]),
        FILTER(
            'Table',
            'Table'[Status] = "C" &&
            CALCULATE(
                MAX('Table'[Date]),
                FILTER(
                    'Table',
                    'Table'[ID] = EARLIER('Table'[ID]) &&
                    'Table'[Status] = "A"
                )
            ) <'Table'[Date]
        )
    )
     
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

Ritaf1983's avatar
Ritaf1983
Super User
3 years ago

Hi Anonymous 
Dax formula for your first request :

Distinct Count A To B =
CALCULATE(
    DISTINCTCOUNT('Table'[ID]),
    FILTER(
        'Table',
        'Table'[Status] = "B" &&
        CALCULATE(
            MAX('Table'[Date]),
            FILTER(
                'Table',
                'Table'[ID] = EARLIER('Table'[ID]) &&
                'Table'[Status] = "A"
            )
        ) <'Table'[Date]
    )
)
 
For Second :
Distinct Count A To C =
CALCULATE(
    DISTINCTCOUNT('Table'[ID]),
    FILTER(
        'Table',
        'Table'[Status] = "C" &&
        CALCULATE(
            MAX('Table'[Date]),
            FILTER(
                'Table',
                'Table'[ID] = EARLIER('Table'[ID]) &&
                'Table'[Status] = "A"
            )
        ) <'Table'[Date]
    )
)
 
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you this is perfect 🙂 

    • Ritaf1983's avatar
      Ritaf1983
      Super User
      3 years ago

      It was my pleasure to assist 🙂