use a date to filter one table and then use that filter to calculate a value for another table

[philseim]

Please help.

I am trying to filter a table based on a nother table but then use a date filter on top of that.

I have three table a date table a client table and a submissions table.

The date table is a normal calendar table which I want to use as a slicer. I am trying to build a matrix where users can select the period where I can see when a client has become a first time claimant, and then see there subsequent submissions for the year. 

A simplified example:

ClientTable
First Claim Date	Client Code
01/15/2018	ABC
01/23/2018	BED

Submission Table
Sent Date	Client Code	Amount
01/15/2018	ABC	100
09/22/2018	ABC	120
01/23/2018	BED	240
01/20/2018	EFG	130

I want to get to a final matrix that should look like this if the date filter for the year was selected.

	Jan	September
Number first time claimants	2
Submission Vlaue	340	120

EFG shouldn't be in the value as it should be excluded as its not a first time claimant for the period

[v-lili6-msft]

hi, @philseim

After my test, you could do these follow my steps as below:

Step1:

Merge two table( ClientTable and Submission Table) in Edit Queries as new query( or on the basis of the original table)

Step2:

expand the table  and Cloes&Apply

Step3:

Create a type table as row value of matrix

Step4:

Create the relationship between merge table and date table

Step5

create a column that to judge if the Client Code it is not a first time claimant for the period

first time claimant = LOOKUPVALUE(Merge1[Client Code],Merge1[ClientTable.Client Code],Merge1[Client Code])

Step6:

Add this measure

measure = SWITCH(TRUE()
,SELECTEDVALUE(Table1[Type])="Number first time claimants",CALCULATE(COUNTA(Merge1[ClientTable.Client Code]))
,SELECTEDVALUE(Table1[Type])="Submission Vlaue",CALCULATE(SUM(Merge1[Amount]
)))

Step7:

Drag the field into matrix and drag the column first time claimant into visual level filter and set filter blank value

here is pbix file, please try it.

https://www.dropbox.com/s/deeqeer7cy81o95/use%20a%20date%20to%20filter%20one%20table%20and%20then%20...

Best Regards,

Lin

[philseim]

Thanks but I think I've left something out which is part of the question. So in  my example I put client EFG out the client table but in reality they would be a first time claimant but the date would be out the range I was looking at depending on the slicer.

If for example my date slicer was for the year 2018 and EFG was a first time claimant before that say for example 7/7/2017, they shouldn't be included and their submissions won't be in. The problem is with the lookup they will look as though they are. Is there a way to do a lookup on that column and date?