Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
snsrkrishna
Regular 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
v-yulgu-msft
Employee
Employee

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.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
Employee

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.
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.