Help with measure - count incidents between given dates

DavidAtkins — Fri, 27 Sep 2024 14:18:19 GMT

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

Re: Help with measure - count incidents between given dates

Selva-Salimi — Fri, 27 Sep 2024 15:08:35 GMT

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.

Re: Help with measure - count incidents between given dates

DavidAtkins — Fri, 27 Sep 2024 15:22:25 GMT

Fantastic, thank you so much

topic Re: Help with measure - count incidents between given dates in DAX Commands and Tips

Help with measure - count incidents between given dates

Re: Help with measure - count incidents between given dates

Re: Help with measure - count incidents between given dates