Counting number of occurrences in a month

[Anonymous]

Hello, I have the following dataset in Power BI and I'm interested in adding a column to see how many times a client visited in a calendar month. 

Data:

Client_ID	Date	Month
001	1/5/2014	2014/1
002	1/6/2014	2014/1
003	1/7/2014	2014/1
004	1/8/2014	2014/1
005	1/10/2014	2014/1
006	1/11/2014	2014/1
007	1/12/2014	2014/1
008	1/13/2014	2014/1
001	1/15/2014	2014/1
002	1/17/2014	2014/1
004	1/20/2014	2014/1
006	1/21/2014	2014/1
001	1/21/2014	2014/1
002	1/22/2014	2014/1
006	1/23/2014	2014/1
002	2/1/2014	2014/2
001	2/3/2014	2014/2
003	2/4/2014	2014/2

Wanted Result:

Client_ID	Date	Month	N Visits that month
001	1/5/2014	2014/1	3
002	1/6/2014	2014/1	3
003	1/7/2014	2014/1	1
004	1/8/2014	2014/1	2
005	1/10/2014	2014/1	1
006	1/11/2014	2014/1	3
007	1/12/2014	2014/1	1
008	1/13/2014	2014/1	1
001	1/15/2014	2014/1	3
002	1/17/2014	2014/1	3
004	1/20/2014	2014/1	2
006	1/21/2014	2014/1	3
001	1/21/2014	2014/1	3
002	1/22/2014	2014/1	3
006	1/23/2014	2014/1	3
002	2/1/2014	2014/2	1
001	2/3/2014	2014/2	1
003	2/4/2014	2014/2	1

Thanks in advance!

[Zubair_Muhammad]

HI @Anonymous

Try this column

N Visits =
CALCULATE (
    COUNT ( TableName[Date] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[Client_ID] ),
        MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) )
    )
)

[Anonymous]

Hi @Zubair_Muhammad 

In the same example, how can we calculate the visit in the last X days? 

Could you help with that please? 

Thank you 

Ilky

[Anonymous · Accepted solution]

I got an error on your column, but I figured it out. Here is the column I created:

Monthly Visits =

CALCULATE(

    COUNT(Congregate_Only_Logs[Client_ID]),

    FILTER(Congregate_Only_Logs,

    Congregate_Only_Logs[Month]=EARLIER(Congregate_Only_Logs[Month])),

    FILTER(Congregate_Only_Logs,

        Congregate_Only_Logs[Client_ID]=EARLIER(Congregate_Only_Logs[Client_ID])))

Thanks!!!

[Sneha1122]

Hi @Zubair_Muhammad 

This formula worked for me to capture the number of occurrence of a particular value, by month.

But I have the data since 2021. So this formula has seggregated the number of occurrences of a value in each month, for all the years. For example, Number of occurrences of the value in July, for 2021,2022,2023 and 2024. But what i'm looking for here is, number of occurrences of a value in July 2021, July 2022, July 2023 and July 2024.

Can you help me build a formula, where we can separate the years and months?