Count unique Values in a month

[Kate_McCulloch]

I'm trying to count how many contacts a person has had in a month, the end column is what I would like it to calculate but struggling to find a measure to do this, can anyone help. 

Thank you 

[Ashish_Mathur]

Hi,

Here's another way.  Write this measure

Measure = COUNTROWS(Data)

Write this visual calculation

Sum = CALCULATE([Measure],COLLAPSE(ROWS))

[Anonymous]

Hi @Kate_McCulloch,

Thanks for reaching out to the Microsoft fabric community forum. It seems you want to calculate for each row (person + contact date), the count of how many contacts that same person had in that same calendar month (and year). As @Cookistador has responded to your query with a measure, kindly go through his response and check if your issue can be resolved.

I would also take a moment to thank @Cookistador and @bhanu_gautam, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[Anonymous]

Hi @Kate_McCulloch,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround so that other users can benefit as well.  And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[Kate_McCulloch]

Hi Apologies, we had alot of system issues last week, the solution from bhaunu_gautam worked to a point but was having issues with a couple not calculating correctly. I wasn't able to further test anything due to the issues last week

[Anonymous]

Hi @Kate_McCulloch,

Can you please confirm once you are able to go through the response provided. Also if you need any help, we’re happy to keep working with you on this. 

Best Regards,

Hammad.

[Anonymous]

Hi @Kate_McCulloch,
Hope everything’s going smoothly on your end. As we haven’t heard back from you, so I wanted to check if you were able to go through the response provided.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Best Regards,

Hammad.

[Anonymous]

Hi @Kate_McCulloch,
We noticed there hasn’t been any recent activity on this thread. If you still need support, just drop a reply here and we’ll pick it up from where we left off.

Best Regards,

Hammad.

[Cookistador]

Hi @Kate_McCulloch 

The following dax measure should work

Contacts in Month =

VAR CurrentPerson = SELECTEDVALUE('Table'[UNIQUE ID])

VAR CurrentMonth = MONTH(SELECTEDVALUE('Table'[Contact Date]))

VAR CurrentYear = YEAR(SELECTEDVALUE('Table'[Contact Date]))

RETURN

CALCULATE(

    COUNTROWS('Table'),

    FILTER(

        ALL('Table'),

        'Table'[UNIQUE ID] = CurrentPerson &&

        MONTH('Table'[Contact Date]) = CurrentMonth &&

        YEAR('Table'[Contact Date]) = CurrentYear

    )

)

(I modified a little bit your sample 🙂 )

[bhanu_gautam · Accepted solution]

@Kate_McCulloch , Try using

Contacts in Month =
CALCULATE(
COUNT('Table'[Contact Date]),
FILTER(
'Table',
'Table'[UNIQUE ID] = EARLIER('Table'[UNIQUE ID]) &&
MONTH('Table'[Contact Date]) = MONTH(EARLIER('Table'[Contact Date])) &&
YEAR('Table'[Contact Date]) = YEAR(EARLIER('Table'[Contact Date]))
)
)