Help with Date

[user900]

I have a data file that includes a Report Month field (10/1/2022 through 10/1/2023).  Data type is set as Date.  I want to create a measure to get the distinct count of IDs on or before 7/1/2023.  But when I do, it assumes 10/1/2023 is within that period.  Why and what do I need to fix it?

Thanks for any help.

[Uzi2019]

hi @user900 

can you do the following calculation:
Total Count= CALCULATE(DISCTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Date]<=DATE(2023,07,01)))

If my post helps please give kudos and accept it as a solution!
Thanks

[Uzi2019 · Accepted solution]

Hi @user900 
Please try below measure

Total Count= CALCULATE(COUNT('Table'[ID]),FILTER('Table','Table'[Date]<=DATE(2023,07,01)))

If my post helps please give kudos and accept it as a solution!
Thanks

[user900]

Fantasic!  I did make a minor change.

Total Count= CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Date]<=DATE(2023,07,01)))

Thank you so much.

[Ritaf1983]

Hi @user900 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[user900]

Sample data:

Report Month	ID
10/1/2022	683
11/1/2022	683
12/1/2022	683
1/1/2023	683
2/1/2023	683
3/1/2023	683
4/1/2023	683
5/1/2023	683
6/1/2023	683
7/1/2023	683
8/1/2023	683
9/1/2023	683
10/1/2023	683
10/1/2022	160
11/1/2022	160
12/1/2022	160
1/1/2023	160
2/1/2023	160
3/1/2023	160
4/1/2023	160
5/1/2023	160
6/1/2023	160
7/1/2023	160
8/1/2023	160
9/1/2023	160
10/1/2023	160

I'm expecting for the months Oct 2022 through July 2023 to have an outcome of 2.  The issue I have is it also returns 2 for Oct 2023 and shouldn't.

My measure is: CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[Report Month]<="7/1/2023")

Report Month	Expected Outcome
10/1/2022	2
11/1/2022	2
12/1/2022	2
1/1/2023	2
2/1/2023	2
3/1/2023	2
4/1/2023	2
5/1/2023	2
6/1/2023	2
7/1/2023	2
8/1/2023
9/1/2023
10/1/2023