Counting the number of items for a certain date

[JViggiani]

Hi everyone,

I have the database ESD_ReqIncProb for a service desk ticket system open as a table in PowerBI. I also have defined a second dable which is just a list of Dates.

ESD_ReqIncProb is just a list of the tickets and their details and has the following relevent information on it:

• Ticket Reference [EFOLDERID]
• Current ticket status (opened, closed) [Status]
• Date / time opened [tStartDateTime]
• Date closed [ClosedDate]

Currently, if I try to plot a graph of the count of number of EFOLDERIDs, on an axis of tStartDateTime, filtered by status, I get "the current status of tickets currently sitting in our queue in the queue from eg October 2017 to eg November 2017".

What I am after is a graph of "the number of tickets originally opened / closed in October 2017 to November 2017" so that we can see whether more tickets are being opened or closed.

There is a big difference between the two. The former can be looked at simply by looking at the current "status" of the ticket but the status changes over time.

I created a new table (Date) with a list of dates in it defined by the oldest ticket. This was done with the following formula:

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

This just prints a column of dates in a new table called Date between the date of the oldest logged ticket and the current day.

I then would like for the system to count the number of tickets opened on each one of these dates. I have tried the 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 all of the entries [EFOLDERID] in the database ESD_ReqIncProb
2. Filter this related table to only count the values where the date of the ticket opened is equal to the Date present on the ajacent row.

Instead, this counts all tickets. The filter seems to do nothing.

Please can someone help me?

Cheers!

[Ashish_Mathur]

Hi,

Share the link from where i can download your file and show the expected result.

[dramus · Accepted solution]

You are close, this worked for me. I added the column to the Dates table.

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