cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Hi, Can you helped me on to count only open ticket for each month?

Hi,

I have a below table :

Ticket Date CreatedDate ClosedMonth it is open      
    JanuaryFebruaryMarchAprilMayJune
11-Jan31-JanJan,Jan     
21-Jan1-FebJan,FebJanFeb    
315-Mar1-JunMar,Apr,May,Jun  MarAprMayJun
431-May1-JunMay,Jun    MayJun
58-Feb1-AprFeb,Mar,Apr FebMarApr  
61-May15-MayMay    May 
731-Jan1-JunJan,Feb,Mar,Apr,May,JunJanFebMarAprMayJun
815-Jun18-JunJun     Jun
91-Mar1-MayMar,Apr,May  MarAprMay 
101-Jan2-JanJanJan     

 

and I need to calculate the the count like below:

MonthCount of In Progress
  
Jan4
Feb3
March4
April4
May5
June4

 

so For each ticket add one to the count open/month from the month the ticket was opened through (and including) the month it was closed.

here is my below data source

https://drive.google.com/file/d/1cu-MhCMuPNJiW-p8Pn9a-AkWl-GBqYmL/view?usp=sharing

4 REPLIES 4
Highlighted
Super User IV
Super User IV

@sandip , refer this can help

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

 

Just do distinct count of months



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

Hi @parry2k 

,

@amitchandak,

I have used the below expression,

Carryover Tickets1 =
VAR __mindate = MIN ( 'DimDateDemo'[Date] )
RETURN
CALCULATE (
COUNTROWS( 'Demo Data' ),
'Demo Data'[Date Created] <= __mindate,
OR ( 'Demo Data'[Date Closed] >= __mindate, ISBLANK('Demo Data'[Date Closed]) )
)

and I have created a Date table like below:

DimDateDemo =
GENERATE (
CALENDAR ( FIRSTDATE('Demo Data'[Date Created]), LASTDATE('Demo Data'[Date Created])),
VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
"Day", day,
"Month Number", MONTH ( [Date] ),
"Month Name", FORMAT ( [Date], "MMMM" ),
"Year", year,
"Month Year", FORMAT ( [Date], "MMMM-YYYY" ),
"Short Month", FORMAT ( [Date], "MM" ),
"Short Month Year", FORMAT ( [Date], "MMM-YY" ),
"Month Year Number", FORMAT ( [Date], "YYMM" )
)
)
and there is no relation between them, but I am getting below results:

 

Highlighted

Hi @sandip,

 

have you solved this or are you still struggeling with it?

 

Cheers,
Sturla

Highlighted
Super User II
Super User II

Hi @sandip ,

 

See if the following thread helps:

https://community.powerbi.com/t5/Desktop/Count-tickets-opened-and-closed-in-the-same-month/m-p/97092...

 

Thanks,

Pragati





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors