Count rows in unrelated table based on date

[Anonymous]

HI together,

I have a problem that has been making me despair for days.

Actual description:
Currently there are two tables. The first table contains all incidents with the attributes ID, ActualStart, ActualFinish. This table is with the Date table where all dates are located. Both tables are connected by Date and Actual Start.

Two KPI are needed:
One is to count how many tickets were opened in the month, which turns out to be pretty simple.
DAX: Created_Tickets = COUNTROWS(RELATEDTABLE(Security_Incidents_Created))

The second number is a bit harder to calculate and makes me despair.
Due to the fact that the connection between ActualStart and Date was drawn, I can't manage to count the rows where Closed Month is equal to Date.
Keyword: Countrows to an unrelated table.
The attempt to solve this with a variable failed.

Closed_Ticket = var cont = FIRSTNONBLANK('Security_Incidents_Created'[Closed Month],1) return COUNTROWS(FILTER('Date','Date'[Date]=cont))

In addition, tickets can still be open if Closed Month is empty.

Could somebody please help?

[amitchandak · Accepted solution]

@Anonymous , Seem like very similar to my HR blog topic

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970