Need to calculate occurrence of an object between two dates from an excel

[Anonymous]

Hello Guys,

I want to calculate the count of Ip address repeated between the createion date and  90 days ago this creation date 
plz help me how to calculate 

Ip                   Creation Date   creation date 90 days ago

10.10.122.10    1/1/2021         3/10/2020

12.12.199.10     4/1/2021        6/10/2021
10.10.122.10    1/1/2021          3/10/2021
13.12.12.10      1/1/2021          3/10/2020

result should be 
count
1
1
2
1

Thanks

Pra137

[amitchandak]

@Anonymous , a new column

New colume =
var _dt1 = [Creation Date] - 90
var _dt2 = [Creation Date] + 90
return
countx(filter(Table, [Creation Date] >=_dt1 && [Creation Date] <=_dt2), [ip])

[Anonymous]

It show me the total count of Ip address in column like 1999 for all rows i want to see 1,2,3 count of ip

[Anonymous · Accepted solution]

Hi @Anonymous 

Try this code to create a calculated column.

Count =
CALCULATE (
    COUNTROWS ( 'Sample' ),
    FILTER (
        'Sample',
        AND (
            'Sample'[Ip] = EARLIER ( 'Sample'[Ip] ),
            'Sample'[creation date 90 days ago]
                <= EARLIER ( 'Sample'[creation date 90 days ago] )
        )
    )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.