Count number of items between a date

[JViggiani]

Hi everyone,

I have a database for a service desk ticketing system stored in a table ESD_ReqIncProb which contains the following relevent information:

• EFOLDERID - A unique ticket ID assigned to every ticket
• Status - The current status of the ticket today
• tStartDateTime - The date and time for which the ticket was opened
• ClosedDate - The date and time for which the ticket was closed. For open tickets this is blank

I have defined a related table which is just a list of all the dates for which a ticket was opened. This was done with the formula:

Date = CALENDAR ( MIN ( ESD_ReqIncProb[tStartDateTime] ) , TODAY() )

So it prints a column of dates from the minimum value of the day of the earliest ticket opening up until today.

I want to define a new column in the Date table which counts the number of tickets opened for that day. I have tried using the following code:

CallsOpened = 
CALCULATE (
    COUNTA( ESD_ReqIncProb[EFOLDERID] ),
    FILTER (
        ESD_ReqIncProb,
        ESD_ReqIncProb[tStartDateTime].[Date] = RELATED ( 'Date'[Date] )
    )
)

How this should work in theory:

1. Count the total number of tickets
2. Filter by the date in the current row.

Instead this simply returns the total number of tickets for every single row. It just prints

22000

22000

22000

...

etc.

Could anybody help me?

Cheers!

[Greg_Deckler]

As long as your tables are related:

MyConunt = COUNTROWS(RELATEDTABLE('Tickets'))