cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
snsrkrishna Visitor
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
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" )
)

1.PNG

 

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] ) )
)

2.PNG

 

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
scottsen Senior Member
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
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" )
)

1.PNG

 

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] ) )
)

2.PNG

 

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.