Forum Discussion
Solved
Help with Date
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. Bu...
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
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 |