Forum Discussion
Count rows in different months by ID
Hello,
I have a table that follows this structure:
| ID | Date |
| 111 | 28/01/2025 |
| 111 | 12/02/2025 |
| 222 | 17/02/2024 |
| 222 | 28/02/2025 |
| 333 | 03/05/2025 |
| 333 | 13/05/2025 |
| 333 | 21/05/2025 |
| 444 | 01/07/2025 |
I'm trying to create a measure that counts rows where the "Date" column has different months by "ID".
Because I want this measure to show, for each ID, if it has more than one row in different months. I tried to create this code, but it is returning me an error:
Count_Measure =
CALCULATE(
COUNTROWS(MyTable),
ALLEXCEPT(MyTable, MyTable[ID]),
COUNTX(
VALUES(MyTable[Date]),
CALCULATE(DISTINCTCOUNT(MyTable[Date]))
) > 1
)The expected result of this measure in a visual table would be something like this:
| ID | Count_Measure |
| 111 | 2 |
| 222 | 2 |
| 333 | 0 |
| 444 | 0 |
OBS: Note that ID 222 had the count "2" because although the two occurrences were in the same month (February), they are from different years.
How can I do this?
3 Replies
- lbendlin
Super User
- Ashish_Mathur
Super User
Hi,
Shouldn't the answer for 333 and 444 be 1?
- v-sathmakuri
Community Support
Hi nok ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thanks!!