Count DISTINCTVALUE with a FILTER for a specific date

[AW1976NOVA]

Hi,

I have a created measure that I believe should be correct but it is not.

I would like to count the DISTINCTCOUNT of the field 'Eligibility Data'[Member ID (Elig)] with a filter for the specific date of 11/30/2020 in the field 'Eligibility Data'[Coverage Date End (Elig)].

Here is my existing created measure that I've come up with (but it is not returning a value):

Distinct Count of Eligibile Members =

CALCULATE(DISTINCTCOUNT('Eligibility Data'[Member ID (Elig)]), FILTER('Eligibility Data','Eligibility Data'[Coverage Date End (Elig)] = "11/30/2020"))

When I originally bring the data in to Power BI the 'Eligibility Data'[Coverage Date End (Elig)] field is brought in as a text field.  I've gone ahead and transformed the field in Edit Queries from a text field to a Date field.

Is this why my formula is not acknowleging "11/30/2020" as a date?  Is it looking for a text value with "11/30/2020" when I've already transformed it to a date field?

If so, how do I modify my existing created measure to look up 11/30/2020 as a date and not a text value?

[AlB · Accepted solution]

Hi @AW1976NOVA 

Sure, it will be looking for that string. Use

DATE(2020, 11, 30)

instead of

"11/30/2020"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers