Forum Discussion
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. 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.
5 Replies
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/1447523Sample 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
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.
