calculate open tickets by date

[kafil10]

hi,

I have a db that contains tickets a ticket is created in a date and closed in an other,

the ticket is open if he dont have a date closed or he is between the two dates

the question is how in per exemple in 10/07/2016 i can know the exactly number

of tickets with DAX please ??

[v-chuncz-msft · Accepted solution]

@kafil10,

Here is a similar post for your reference.

http://community.powerbi.com/t5/Desktop/Trend-from-History-Table/m-p/200304#M88115

[Greg_Deckler · Accepted solution]

Given a table of Dates, how about a calculated column like this?

OpenTickets = CALCULATE(COUNTROWS(Tickets),FILTER(Tickets,(Tickets[create_ticket]<=[Dates] && ISBLANK(Tickets[close_ticket_date]) || (Tickets[create_ticket]<=[Dates] && Tickets[close_ticket_date]>=[Dates]))))

[v-chuncz-msft · Accepted solution]

@kafil10,

Here is a similar post for your reference.

http://community.powerbi.com/t5/Desktop/Trend-from-History-Table/m-p/200304#M88115

[kafil10]

@Greg_Deckler@v-chuncz-msf

Hi,

what i want really is without the extraction of filter tables, can make the count dynamic in other words the count change when i change a criteria in the filter pane i tested a lot of a possibilities; there is a way to do it without creating too much filter tables ??

france = CALCULATE (
        COUNT ( emea_france[CallID] );
        FILTER (
            emea_france;
            emea_france[NewColumn.RecvdDate] <= [Date]
                && (
                   emea_france[NewColumn.ClosedDate]> [Date]
                        || ISBLANK ( emea_france[NewColumn.ClosedDate] )
                )
        )
    )

[kafil10]

I tried it but it gives me wrong numbers i think its due to the calendar auto date it begin from
1899

[Greg_Deckler · Accepted solution]

Given a table of Dates, how about a calculated column like this?

OpenTickets = CALCULATE(COUNTROWS(Tickets),FILTER(Tickets,(Tickets[create_ticket]<=[Dates] && ISBLANK(Tickets[close_ticket_date]) || (Tickets[create_ticket]<=[Dates] && Tickets[close_ticket_date]>=[Dates]))))

[naomi_j]

Hi Greg,

Attempting to use this to calculate open tickets in my data set - but keep getting the following error on my calendar date: 

A single value for column 'date' in table 'calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

My measure is: 

Opentickets = CALCULATE(COUNTROWS('Master Table'),FILTER('Master Table',('Master Table'[Created].[Date]<= calendar[date] && ISBLANK('Master Table'[Resolved].[Date]) || ('Master Table'[Created].[Date]<=calendar[date] && 'Master Table'[Resolved].[Date]>=calendar[date]))))

Are you able to help?

[Anonymous]

@Greg_Deckler Hi Greg, I'm very much a beginner, but I thought I would try to apply this as well.

I would also like to show how many tickets are open on a specific date. I have a source of tickets (Query1) with a unique number in column 'No' and the fields 'Created_on' and 'Closed_on' with them both being data type 'Date'. Should I then add a column of:

OpenTickets = CALCULATE(COUNTROWS(Query1);FILTER(Query1;(Query1[Created_on]<=[Dates] && ISBLANK(Query1[Closed_on]) || (Query1[Created_on]<=[Dates] && Query1[Closed_on]>=[Dates])))) 

I'm doing something wrong but I'm not sure how to edit the code for it to be correct.

See here my pbix screenshot

Thanks in advance!

[kafil10]

@Greg_Deckler

i think its what i need but if you can help me im a beginner to create the table dates

[Greg_Deckler · Accepted solution]

Easiest way would be to go to the data model and click on New Table and use the following formula:

DateTable = CALENDARAUTO()

Then, flag that column as a Date field versus Date/Time field.

[kafil10]

@Greg_Deckler

hi,

Table 3 = ADDCOLUMNS (
    CALENDAR(MIN(CallLog[RecvdDate]);MAX(CallLog[RecvdDate]));
    "Count"; CALCULATE (
        COUNT (CallLog[CallID]);
        FILTER (
           CallLog;
            CallLog[RecvdDate]<= [Date]
                && (
                   CallLog[ClosedDate] > [Date]
                        || ISBLANK (CallLog[ClosedDate])
                )
        )
    )
)

i did the function its working with the merge of two tables and selecting the values in the merge, what i want to do if its possible
is to count with just filters in the graphic panel if its possible ??

[Greg_Deckler]

If you can supply sample data, I or someone else can supply a specific answer. Otherwise, the general process would be to count all of the tickets with an open date that is less than or equal to the reference date and do not have a close date. Something along the lines of:

CALCULATE(COUNT(Tickets[TicketID]),FILTER(Tickets,Tickets[OpenDate] <= DATE(2016,10,7) && ISBLANK(Tickets[CloseDate])))

[kafil10]

okey sorry this is an example in excel

create_ticket	close_ticket_date	status
10/02/2015	10/02/2015	close
10/02/2015	11/02/2015	close
11/02/2017	17/06/2017	close
20/05/2017		open

what i want in my graphe is to know that for example in the day 10/02/2015
i was having 2 open tickets and in the day 20/05/2017 2 also like i explained ??