Return how many times it appear within a week

[Anonymous]

Hi, 

I am having a month of data and want to create a new column that returns true/false on the devices visit any locations more than 3 times a week before getting this I tried to get the number of visits per week, have tried a few different methods by myself but still could not get it right. If anyone has any ideas, please advise me.

This is a dummy dataset of how the actual data looks like:

Date	Time	Location	Devices	Week	Number of visits per week (This column is what I want)
2020-01-01	5:07:39AM	A	00:11:22:33:44:55	1	2
2020-01-01	7:02:02AM	A	00:11:22:33:44:55	1	2
2020-01-01	8:12: 13 AM	A	00:11:22:33:44:55	1	2
2020-01-01	9:12:54AM	C	66:44:77:88:99:00	1	4
2020-01-01	10:00:12AM	B	00:11:22:33:44:55	1	2
2020-01-02	8:12: 13 PM	B	66:44:77:88:99:00	1	4
2020-01-05	11:02:02AM	A	00:11:22:33:44:55	1	2
2020-01-06	7:42:02AM	A	66:44:77:88:99:00	1	4
2020-01-07	1:23:34PM	C	66:44:77:88:99:00	1	4
2020-01-08	6:07:34AM	A	00:11:22:33:44:55	2	3
2020-01-10	8:22:12AM	A	00:11:22:33:44:55	2	3
2020-01-10	10:22:12AM	C	00:11:22:33:44:55	2	3
2020-01-12	12:23:45PM	B	00:11:22:33:44:55	2	3
2020-01-12	11:22:12AM	C	66:44:77:88:99:00	2	2
2020-01-14	10:43:16AM	B	66:44:77:88:99:00	2	2

I have tried this and but this returned how many times the devices appeared on each date and location: 

Number of visit per week =
COUNTX(
FILTER ('data', EARLIER([Devices]) = [Devices] && EARLIER([Date]) = [Date] && EARLIER([Location]) = [Location]),
'data'[Devices])

Thanks!

[amitchandak]

I tried like this. But seems does not match with your no. Can explain how you got those number

cal week = COUNTX(FILTER(Sheet1,Sheet1[Location]=EARLIER(Sheet1[Location]) && Sheet1[Devices]=EARLIER(Sheet1[Devices]) && Sheet1[Week ]=EARLIER(Sheet1[Week ])),Sheet1[Devices])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous]

Hi, the table is a little misleading because the column line is missing, I am having Date, Time, Location, Devices, Week, Number of visits per week (This is the column I want).

[v-eachen-msft]

Hi @Anonymous ,

Is this table your expected result? If is, could you explain how to get 2 and 4 in first week?

[Anonymous]

Hi @v-eachen-msft, the number of visits per week is the column that I want to find (or calculate), the number of visits per week is based on the devices, I am hoping to find how many days each device appeared per week, no matter which location it appears and how many times it appears at one location. 

[danextian]

Hi @Anonymous ,

Try this although  result didn't match what is in your table (but this is based on your description):

Number of Visits Per Week =
CALCULATE (
    COUNTROWS ( 'Table' ),
    ALLEXCEPT ( 'Table', 'Table'[Week ], 'Table'[Devices] )
)