cancel
Showing results for
Did you mean:
Visitor

## Count of open issues as of today (particular date)

Hi,

I want to calculate the count of not closed tickets for everyday.

I have created another table with the list of dates with below formula.

`DateTable = CALENDAR(MINX(TT, TT[Opened At].[Date]),MAXX(TT,TT[Opened At].[Date]))`

to have a comparision, but could not make it.

I am unable to find a way to count the non closed tickest with respect to a date in "DateTable" row item.

As show below, we wanted have not closed tickets, i.e open tickets as of a day.

Desired output as below.

Table: DateTable.

Date              CountOfTicketsAsOfToday

16-05-2017   3

17-05-2017   8

18-05-2017   11

Table : TicketList

```Ticket ID	       OpenDate	       TicketStatus	Expected count
Y33161599	16-05-2017	Closed
Y33161690	16-05-2017	Open
Y33161741	16-05-2017	Accepted
Y33161758	16-05-2017	Closed
Y33161765	16-05-2017	Accepted	         3
Y33161781	17-05-2017	Open
Y33161794	17-05-2017	Open
Y33161797	17-05-2017	Open
Y33161832	17-05-2017	Open
Y33161861	17-05-2017	Work In Progress
Y33161867	17-05-2017	Closed	           8
Y33161906	17-05-2017	Open
Y33161932	18-05-2017	Open
Y33161943	18-05-2017	Open	           11```

Spoiler

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Count of open issues as of today (particular date)

Hi @snsrkrishna,

In ticket table, create a calculated column like below:

```Count for each day =
CALCULATE (
COUNT ( 'TT'[Open Date] ),
FILTER ( ALLEXCEPT ( 'TT', 'TT'[Open Date] ), 'TT'[Ticket Status] <> "Closed" )
)```

In date table, add two new columns which shows the count of open tickects for everyday.

```Count =
LOOKUPVALUE ( 'TT'[Count for each day], 'TT'[Open Date], 'Date Table'[Date] )

Total =
CALCULATE (
SUM ( 'Date Table'[Count] ),
FILTER ( 'Date Table', 'Date Table'[Date] <= EARLIER ( 'Date Table'[Date] ) )
)```

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Senior Member

## Re: Count of open issues as of today (particular date)

Google/Bing for the term "DAX Events in Progress" and you should find some great resources on this problem.

Community Support Team

## Re: Count of open issues as of today (particular date)

Hi @snsrkrishna,

In ticket table, create a calculated column like below:

```Count for each day =
CALCULATE (
COUNT ( 'TT'[Open Date] ),
FILTER ( ALLEXCEPT ( 'TT', 'TT'[Open Date] ), 'TT'[Ticket Status] <> "Closed" )
)```

In date table, add two new columns which shows the count of open tickects for everyday.

```Count =
LOOKUPVALUE ( 'TT'[Count for each day], 'TT'[Open Date], 'Date Table'[Date] )

Total =
CALCULATE (
SUM ( 'Date Table'[Count] ),
FILTER ( 'Date Table', 'Date Table'[Date] <= EARLIER ( 'Date Table'[Date] ) )
)```

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.