Help with measure - count incidents between given dates

[DavidAtkins]

Hi

I need some help with a measure please! I have two tables as below. Table 1 shows a list of contracts that can renew leading to multiple rows for the same contract but with start/end dates to differentiate them. Table 2 has a list of incidents that have occurred on those contracts by date. How can I create a measure to show the incidents by contract linked to the incident date to give the required results in the bottom example? At the moment the two tables are only connected by contract number (I'm wondering if they need to be linked on the dates too?).

Thanks in advance for any help.

Table 1				Table 2
Contract Number	Contract Start	Contract End		Contract Number	Incident Date
1	01/01/2021	31/12/2021		1	13/01/2021
1	01/01/2022	31/12/2022		1	15/05/2021
2	01/01/2023	31/12/2023		1	20/05/2022
3	01/07/2021	30/09/2021		3	15/11/2021
3	01/10/2021	31/12/2021
3	01/01/2022	31/03/2022
Required Result
Contract Number	Contract Start	Contract End	Incidents
1	01/01/2021	31/12/2021	2
1	01/01/2022	31/12/2022	1
2	01/01/2023	31/12/2023	0
3	01/07/2021	30/09/2021	0
3	01/10/2021	31/12/2021	1
3	01/01/2022	31/03/2022	0

[Selva-Salimi · Accepted solution]

@DavidAtkins 

you just need to write a measure as follows:

measure incident_count := calculate (count(incidentdate) , filter (table 2 , incidentdate >= selectedvalue(contractstart) && incidentdate<= selectedvalue(contractend))

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

[DavidAtkins]

Fantastic, thank you so much