Calculate open jobs

[BA11501Banderso]

Hello, I’m trying to figure out how to be able to calculate open services and be able to show a daily history.  I can’t seem to figure out the DAX.  I’m guessing it needs to be a calculated field saying something like Open Service = Count of Services where Initiation date <= Calendar date and Repair date > Calendar date.  Do I link the date fields to Calendar?  I’m lost.  Thank you in advance!

Service #	Initiation Date	Repair Date
1	1/1/2022	1/6/2022
2	1/2/2022	1/3/2022
3	1/5/2022	1/12/2022
4	1/10/2022	1/15/2022
5	1/11/2022	1/11/2022

Date	Open Services	Description
1/1/2022	1	Service #1 is open
1/2/2022	2	Service #1 and #2 are open.
1/3/2022	1	Service #1 is open, #2 was completed
1/4/2022	1	Service #2 is complete. Service #1 is still open.
1/5/2022	2	Service #1 and 3 are open.
1/6/2022	1	Service #3 is open.  Service #1 is completed.
1/7/2022	1	Service #3 is open.
1/8/2022	1	Service #3 is open.
1/9/2022	1	Service #3 is open.
1/10/2022	2	Service #3 and #4 are open.
1/11/2022	2	Service #3 and #4 are open. Service #5 open and completed same day.
1/12/2022	1	Service # 4 is open.  Service #3 is completed
1/13/2022	1	Service #4 is open.
1/14/2022	1	Service #4 is open.
1/15/2022	0	Service #4 is completed.

[johnt75 · Accepted solution]

You don't need to link the dates to your calendar table. You can define a measure like

Num open tickets = 
var minDate = MIN('Calendar'[Date])
var maxDate = MAX('Calendar'[Date])
return CALCULATE( COUNTROWS(Tickets'), Tickets[Initiation date] <= minDate && 
   ( Tickets[Repair date] >= maxDate || ISBLANK(Tickets[Repair date]) )
)