Forum Discussion

nok's avatar
nok
Icon for Advocate II rankAdvocate II
1 year ago
Solved

Count rows in different months by ID

Hello,
I have a table that follows this structure:

IDDate
111   28/01/2025
11112/02/2025
22217/02/2024
22228/02/2025
33303/05/2025
33313/05/2025
33321/05/2025
44401/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:

IDCount_Measure
111   2
2222
3330
4440

 

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

  • v-sathmakuri's avatar
    v-sathmakuri
    Icon for Community Support rankCommunity 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!!