Count not coming as expected

[damit23183]

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

[v-jialongy-msft · Accepted solution]

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]

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

[damit23183]

Any Other idea to acheive the expected result?

Thanks

[lbendlin]

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

[wini_R]

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]

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