Forum Discussion

damit23183's avatar
damit23183
Icon for Microsoft Employee rankMicrosoft Employee
2 years ago
Solved

Count not coming as expected

Hi,

I am working on to get calculate one measure using dates and compare from different tables.

For example,

TableA Is Date Table

 

Table B is 

Now What I am trying to achieve is,

 

1. Count = 5  - This is Answer I want.

 

As i am counting all ID where they are enrolled yet not death, and if they death then death year mut be less than current year.

 

I would like to apply this condition:

 

ID must enrolled

DeathDate must Null

DeathDate must less than Jan 1st 2024 (1st Jan of Each new year). 

 

In above case person who died in 2024 was in March so it will be count if that person would have died on Jan 1st 2024 then count could have been 4.

 

Hope this make sense. If you have a question please message me.

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi damit23183 

     

    According to your description, you want counting all ID where they are enrolled yet not death, But your needs: DeathDate must less than Jan 1st 2024 (1st Jan of Each new year , This criterion counts both IDs with death dates of 2/10/2023 and 10/31/2022, when in fact both of them are actually dead.

     

    So I'm suggesting that your criteria should be changed and then you can get the results you want.

    Count = 
CALCULATE(
    COUNT('Table'[EnrolmentDate]),
    FILTER(
        'Table',
        ISBLANK('Table'[DeathDate]) || 'Table'[DeathDate] > DATE(YEAR(NOW()), 1, 1)
    )
)

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • wini_R's avatar
    wini_R
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    Hi damit23183,
    The expected result should be 5 or 6? Actually I can see 6 rows when Death Date is either null or less than Jan 2024

     

    As a starting point you could use this formula:

    CountDates = 
CALCULATE(
    COUNT('Table'[Enrolment]),
    FILTER(
        'Table',
        ISBLANK('Table'[Death]) || 'Table'[Death] < DATE(YEAR(NOW()), 1, 1)
    )
)

     

    • damit23183's avatar
      damit23183
      Icon for Microsoft Employee rankMicrosoft Employee
      2 years ago

      Thanks Wini for your response.

       

      Yes I am also getting 6 with many combination I have tried. But, I want to have 5 only as an answer.

       

      Thanks

  • damit23183's avatar
    damit23183
    Icon for Microsoft Employee rankMicrosoft Employee
    2 years ago

    Any Other idea to acheive the expected result?

     

    Thanks

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      Change your criteria.  With the given criteria and the sample data provided the answer is 6.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi damit23183 

     

    According to your description, you want counting all ID where they are enrolled yet not death, But your needs: DeathDate must less than Jan 1st 2024 (1st Jan of Each new year , This criterion counts both IDs with death dates of 2/10/2023 and 10/31/2022, when in fact both of them are actually dead.

     

    So I'm suggesting that your criteria should be changed and then you can get the results you want.

    Count = 
CALCULATE(
    COUNT('Table'[EnrolmentDate]),
    FILTER(
        'Table',
        ISBLANK('Table'[DeathDate]) || 'Table'[DeathDate] > DATE(YEAR(NOW()), 1, 1)
    )
)

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • damit23183's avatar
      damit23183
      Icon for Microsoft Employee rankMicrosoft Employee
      2 years ago

      Thank you so much for your response.

       

      Thats worked perfectly as expected, was missing DATE conversion in my formula which was causing unexpected result.

       

      Thank you again so much.

       

      Thanks