Mesure for finding occurrence 7 times in 7 consecutive days

[Anonymous]

I have a query I tried to resolve but I failed badly. I have a table below:

Date	LinkName
15/08	A
16/08	A
17/08	A
18/08	A
19/08	A
20/08	A
21/08	A
15/08	B
16/08	B
17/08	B
18/08	B
19/08	B
20/08	B
21/08	B
22/08	A
23/08	B
24/08	B
25/08	A
26/08	A
27/08	A

and how i get a result like this 

Date	find occurrence 7 times in 7 consecutive days
15/08	0
16/08	0
17/08	0
18/08	0
19/08	0
20/08	0
21/08	2
22/08	0
23/08	0
24/08	0
25/08	0
26/08	0
27/08	0

I can get this problem in excel,  but it is hard for me to do it in PowerBi. Iam trying my best to learn PowerBi. Hopefully anyone can help me solve this problem. 

[Greg_Deckler]

@Anonymous - Perhaps:

Column =
  VAR __Table = SUMMARIZE(FILTER('Table',[LinkName]=EARLIER([LinkName]) && [Date]<=EARLIER([Date]) && [Date]>=EARLIER([Date])-7),[LinkName])
RETURN
  IF(COUNTROWS(__Table)=7,1,0)

[Anonymous]

@Greg_Deckler 

[Greg_Deckler · Accepted solution]

@Anonymous - Sorry, I made this more complicated than necessary. See Table (25) of attached PBIX below sig.

Column = 
  VAR __Table = FILTER('Table (25)',[LinkName]=EARLIER([LinkName]) && [Date]<=EARLIER([Date]) && [Date]>=EARLIER([Date])-7)
RETURN
  IF(COUNTROWS(__Table)=7,1,0)

[Anonymous]

@Greg_Deckler yeah, U got it. I still have question, U used calculated collumn to solve this, but my table is very large ( ~ 500000 rows ). So Could we use Mesure instead of Calculated collumn. 

[Greg_Deckler]

@Anonymous - You could, that would look like this:

Column = 
  VAR __Link=MAX([LinkName])
  VAR __Date=MAX([Date])
  VAR __Table = FILTER('Table (25)',[LinkName]=__Link && [Date]<=__Date && [Date]>=__Date-7)
RETURN
  IF(COUNTROWS(__Table)=7,1,0)

Would have to do some additional experimenting to get it to aggregate by Date (so you have just date in your visual and you want 2 to appear for A and B consequetive runs. This assumes you have LinkName in the visual. But, all that said, I *think* you will be happier with a column. This is because it runs a single time during data load. Otherwise, your measure will have to constantly recaculate while you are browing the report and that will be slooooooowwww!! 🙂 

[Anonymous]

It returns 0 for all rows. Maybe sth wrong.