Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
Hi @user900
Please try below measure
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
Hi @user900
Please try below measure
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.
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 |
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
65 | |
62 | |
55 |
User | Count |
---|---|
179 | |
108 | |
104 | |
71 | |
70 |