Tickets over time

Hi locka  - you're on the right track! Since tickets can have multiple entries and may reopen.

I hope you already have a date table, if not please create it using calcualted table.

another table 

Tickets_Expanded =
VAR TicketDates =
SELECTCOLUMNS (
FILTER (
CROSSJOIN ( 'DateTable', 'Tickets' ),
'DateTable'[Date] >= 'Tickets'[From] &&
('Tickets'[To] = BLANK() || 'DateTable'[Date] <= 'Tickets'[To])
),
"Date", 'DateTable'[Date],
"TicketID", 'Tickets'[Reg],
"Team", 'Tickets'[Team]
)
RETURN
TicketDates

create a measure to count the tickets

Active Tickets =
CALCULATE (
DISTINCTCOUNT ( Tickets_Expanded[TicketID] ),
CROSSFILTER ( 'DateTable'[Date], Tickets_Expanded[Date], BOTH )
)

For distinct tickets create another measure 

Active Tickets by Team =
CALCULATE (
DISTINCTCOUNT ( Tickets_Expanded[TicketID] ),
CROSSFILTER ( 'DateTable'[Date], Tickets_Expanded[Date], BOTH ),
VALUES ( Tickets_Expanded[Team] )
)

Hope this helps.

Hey locka 
Please try follwoing code , it may work

Open Tickets =
VAR SelectedDates =
FILTER(
ADDCOLUMNS(
ALL('Tickets'),
"IsOpen",
'Tickets'[From] <= MAX('DateTable'[Date]) &&
(ISBLANK('Tickets'[To]) || 'Tickets'[To] >= MIN('DateTable'[Date]))
),
[IsOpen] = TRUE
)
RETURN
DISTINCT(SELECTCOLUMNS(SelectedDates, "TicketID", 'Tickets'[Reg]))

Regards
Regards
Govind Sapkade ( Data Analyst , Power BI PL 300 Certified , MS Fabric Enthusiast )
Linkdin : www.linkedin.com/in/govind-sapkade-845104225
Youtube : http://www.youtube.com/@govind_dataanalyst

Good morning everyone.  It's been a while since my orginal post my requirments have changed a little.  Although for my inital report I was able to fix it with dax measure similar to below.  I now need to be more procises as tickets can be past between multiple people and or teams across the same peroid including same day.    I'm still looking to do a distinct count number of ticket id over a selected period. 

This is to be used in a thin report which links to a dataset.  (They are sepreate files.)  I need this to caculate this as measure rather than a caculated table or power query etc.  I also want to make sure it can work with a drill through with list of id to allow viewer further insite.

My main issue is there there they can be multiple entries for the same ID over the period, either on the same day or different days.  Viewer of the report can filter by a date period or the agent id or team id and bring in those tickets which match.    It's basically a tickets over time. 

While I already have DAX code which calculates a distinct count of tickets.  When filtered by agent or team the distinct count of id and then shown it in a matrix.  The matrix is split by person which means tickets get counted more than once currently. 

Ideally Id like to count max date time of the period per id so it would only count it once by who ever last had the ticket.  That way I can can get an accurate breakdown of how many tickets an agent or team had in period selected.  So that we can go back in the past and see how many tickets a team or agent had.

VAR MaxDate =
    MAX ( '@DIM_Date'[Date]  ) // Newest date in the date table.

VAR MinDate =
    MIN (  '@DIM_Date'[Date]  ) // Earlist date in the date table.

VAR Result =
CALCULATE(
    DISTINCTCOUNT(FACT_FaultAssignmentHistory[Faultid]),
    FACT_FaultAssignmentHistory[FromDate] <= MaxDate // Include tickets upto the end of the period.
    ,
    (
     FACT_FaultAssignmentHistory[ToDate] >= MinDate
       || ISBLANK ( FACT_FaultAssignmentHistory[ToDate] ) //Include tickets that are already open or create in the period.
    ),
    (
     FACT_Faults[ResolvedDate] >= MinDate
       || FACT_Faults[BeenResolved] = 0   //Includes those tickets which have a resolved date within period or have yet to be resolved.
    ), 
    FACT_FaultAssignmentHistory[AssignedAgentID] IN VALUES('@DIM_Agent'[AgentID]) &&
    FACT_FaultAssignmentHistory[AgentTeam] IN VALUES('@DIM_Teams'[AgentTeam])
)

RETURN
    Result

Here is a sample of data below of entry every time a ticket I also have date field as well as date time ones.  This table is linked to fact table via a relationship a 1 to many.  Every time a ticket moved to another person or team.  It is recorded in the fact table below.  A separate table has list of all the tickets and other information such as has the ticket been closed or not .  I have disconnected datetable , agent and teams tables due to the fact they need to refer to multiple tables and or the same table multiple times for different reasons.  

Table Name:   Fact_FaultsAssignmentHistory