create a table filtering sales activity information by the availability date of sales person

[Anonymous]

So I want to show all the People and the activity logged against them where their date available is -7 days, -14 days etc. and visualise in a graph/table. 

I have a Candidates table (with date available) joined to Candidates Activity on a unique ref with a Dates table joined on the Candidates availability date. The date of the activities is also joined to the Dates table.

Thanks gang

[Anonymous]

Tried this but can't get it to work 

Table = CALCULATETABLE('CandidateActivity',FILTER('Candidates','Candidates'[date_available],DATESBETWEEN('Candidates'[date_available],DAY(-7),now())))

[Anonymous]

This is the table outcome I am after. I'm alittle worried this isn't doable? However you guys always seem to pull something out the bag.

Candidate

Status

Date Available

Week -4

Total activity in week

Week -3

Total activity in week

Week -2

Total activity in week

Week -1

Total activity in week

Week 0

Total activity in week

Week +1

Total Activity

Bob Smith

New Arrival

9th June 2017

Calls

2

Calls

0

Email Sent

3

Interview

1

Email Sent

3

9

Meeting

1

Meeting

0

Calls

1

Calls

5

Calls

1

8

CV Send out

5

CV Send out

3

CV Send out

0

CV Send out

1

CV Send out

0

9

Greg Jones

Actively Seeking

7th Jul 2017

Calls

2

Meeting

1

CV Send out

5

[v-caliao-msft]

@Anonymous,

You needn't to create another to filter the date. Just create a measure in your original table, and you can get your expected table. The steps below is for you reference.

1. Create a table by Enter Date.
   
2. Create a measure in your table.
   Measure =
   var selecteditem = MAX(Table1[Type])
   var dateinterval = IF(selecteditem="Last 7 Days",7,14)
   var today =TODAY()
   return IF(1*(today-MAX('Date'[Date]))>=0&&1*(today-MAX('Date'[Date]))<dateinterval,1,0)
3. Use this table in your visual filter.
   

Regards,

Charlie Liao